Query based on field in form, not working in 2007

Hi

After upgrading to Access 2007 (from 2003), I have a query that has stopped 
working. It is something like

SECELT id
FROM [tbl names]
WHERE ([tbl names].firstname = [forms]![My form][firstname]);

It does not seem to register the value of the "firstname" textbox in the 
form [My Form]. Accordign to the SQL profiler, the query just sends an empty 
field "". Therefore instead of searching for a specific entry, I get all my 
entries... The query seemed to work fine in Access 2002 and 2003.

When using 

WHERE ([tbl names].firstname LIKE "*" & [forms]![My form][firstname] & "*"); 

the result is the same.
0
Utf
10/3/2007 3:40:03 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1541 Views

Similar Articles

[PageSpeed] 47

Yes, sorry about that, typo... Any ideas to what could be wrong? The query is 
executed from within the form as part of filter command.

/Martin

"KARL DEWEY" wrote:

> I do not know if it was a typo but your post is missing an exclamination mark 
> to be like this ---
> SECELT id
> FROM [tbl names]
> WHERE ([tbl names].firstname = [forms]![My form]![firstname]);
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Martin Edwards" wrote:
> 
> > Hi
> > 
> > After upgrading to Access 2007 (from 2003), I have a query that has stopped 
> > working. It is something like
> > 
> > SECELT id
> > FROM [tbl names]
> > WHERE ([tbl names].firstname = [forms]![My form][firstname]);
> > 
> > It does not seem to register the value of the "firstname" textbox in the 
> > form [My Form]. Accordign to the SQL profiler, the query just sends an empty 
> > field "". Therefore instead of searching for a specific entry, I get all my 
> > entries... The query seemed to work fine in Access 2002 and 2003.
> > 
> > When using 
> > 
> > WHERE ([tbl names].firstname LIKE "*" & [forms]![My form][firstname] & "*"); 
> > 
> > the result is the same.
0
Utf
10/3/2007 5:33:04 PM
Did you try opening the form, enter first name, then separately run the query 
to see what the results are?
-- 
KARL DEWEY
Build a little - Test a little


"Martin Edwards" wrote:

> Yes, sorry about that, typo... Any ideas to what could be wrong? The query is 
> executed from within the form as part of filter command.
> 
> /Martin
> 
> "KARL DEWEY" wrote:
> 
> > I do not know if it was a typo but your post is missing an exclamination mark 
> > to be like this ---
> > SECELT id
> > FROM [tbl names]
> > WHERE ([tbl names].firstname = [forms]![My form]![firstname]);
> > 
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Martin Edwards" wrote:
> > 
> > > Hi
> > > 
> > > After upgrading to Access 2007 (from 2003), I have a query that has stopped 
> > > working. It is something like
> > > 
> > > SECELT id
> > > FROM [tbl names]
> > > WHERE ([tbl names].firstname = [forms]![My form][firstname]);
> > > 
> > > It does not seem to register the value of the "firstname" textbox in the 
> > > form [My Form]. Accordign to the SQL profiler, the query just sends an empty 
> > > field "". Therefore instead of searching for a specific entry, I get all my 
> > > entries... The query seemed to work fine in Access 2002 and 2003.
> > > 
> > > When using 
> > > 
> > > WHERE ([tbl names].firstname LIKE "*" & [forms]![My form][firstname] & "*"); 
> > > 
> > > the result is the same.
0
Utf
10/3/2007 6:23:01 PM
Unfortunately yes, but with no result. The funny thing is that it worked like 
a charme in 2003. Even if I write something false instead of [firstname] like

WHERE ([tbl names].firstname = [forms]![My form]![firstvsdklvnname]);

it returns the same results.

/Martin

"KARL DEWEY" wrote:

> Did you try opening the form, enter first name, then separately run the query 
> to see what the results are?
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Martin Edwards" wrote:
> 
> > Yes, sorry about that, typo... Any ideas to what could be wrong? The query is 
> > executed from within the form as part of filter command.
> > 
> > /Martin
> > 
> > "KARL DEWEY" wrote:
> > 
> > > I do not know if it was a typo but your post is missing an exclamination mark 
> > > to be like this ---
> > > SECELT id
> > > FROM [tbl names]
> > > WHERE ([tbl names].firstname = [forms]![My form]![firstname]);
> > > 
> > > -- 
> > > KARL DEWEY
> > > Build a little - Test a little
> > > 
> > > 
> > > "Martin Edwards" wrote:
> > > 
> > > > Hi
> > > > 
> > > > After upgrading to Access 2007 (from 2003), I have a query that has stopped 
> > > > working. It is something like
> > > > 
> > > > SECELT id
> > > > FROM [tbl names]
> > > > WHERE ([tbl names].firstname = [forms]![My form][firstname]);
> > > > 
> > > > It does not seem to register the value of the "firstname" textbox in the 
> > > > form [My Form]. Accordign to the SQL profiler, the query just sends an empty 
> > > > field "". Therefore instead of searching for a specific entry, I get all my 
> > > > entries... The query seemed to work fine in Access 2002 and 2003.
> > > > 
> > > > When using 
> > > > 
> > > > WHERE ([tbl names].firstname LIKE "*" & [forms]![My form][firstname] & "*"); 
> > > > 
> > > > the result is the same.
0
Utf
10/3/2007 7:01:01 PM
Have you tried opening the form, enter first name, then separately run this 
query  ---
SELECT AnyTableName.*, [forms]![My form]![firstname] AS My_Guess
FROM AnyTableName;

See what is displayed for My_Guess.

-- 
KARL DEWEY
Build a little - Test a little


"Martin Edwards" wrote:

> Unfortunately yes, but with no result. The funny thing is that it worked like 
> a charme in 2003. Even if I write something false instead of [firstname] like
> 
> WHERE ([tbl names].firstname = [forms]![My form]![firstvsdklvnname]);
> 
> it returns the same results.
> 
> /Martin
> 
> "KARL DEWEY" wrote:
> 
> > Did you try opening the form, enter first name, then separately run the query 
> > to see what the results are?
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Martin Edwards" wrote:
> > 
> > > Yes, sorry about that, typo... Any ideas to what could be wrong? The query is 
> > > executed from within the form as part of filter command.
> > > 
> > > /Martin
> > > 
> > > "KARL DEWEY" wrote:
> > > 
> > > > I do not know if it was a typo but your post is missing an exclamination mark 
> > > > to be like this ---
> > > > SECELT id
> > > > FROM [tbl names]
> > > > WHERE ([tbl names].firstname = [forms]![My form]![firstname]);
> > > > 
> > > > -- 
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > > 
> > > > 
> > > > "Martin Edwards" wrote:
> > > > 
> > > > > Hi
> > > > > 
> > > > > After upgrading to Access 2007 (from 2003), I have a query that has stopped 
> > > > > working. It is something like
> > > > > 
> > > > > SECELT id
> > > > > FROM [tbl names]
> > > > > WHERE ([tbl names].firstname = [forms]![My form][firstname]);
> > > > > 
> > > > > It does not seem to register the value of the "firstname" textbox in the 
> > > > > form [My Form]. Accordign to the SQL profiler, the query just sends an empty 
> > > > > field "". Therefore instead of searching for a specific entry, I get all my 
> > > > > entries... The query seemed to work fine in Access 2002 and 2003.
> > > > > 
> > > > > When using 
> > > > > 
> > > > > WHERE ([tbl names].firstname LIKE "*" & [forms]![My form][firstname] & "*"); 
> > > > > 
> > > > > the result is the same.
0
Utf
10/3/2007 9:50:01 PM
Reply:

Similar Artilces:

Functions in form
I have made an invoice using a form, and have it nearly completed, I have two functions that I don't know how to create. One is simple, I need an automatic number created for each separate invoice printed. And the hard one is I need a function that will give me customer information (name, address, city, state, zip) when I type in a customer code like mcg = mcgregor, 123 fake st., d-town, WA 99999. I have literally looked everywhere on how to do this, even forums. "Zera" <Zera@discussions.microsoft.com> wrote in message news:7DD602A9-E33D-4489-9188-DED9006AD882@micr...

fields
anyone know if you can put in a formula to automatically populate a field. I created the attributes in deployment manager.. added the fields. but the third field I want to be a product of the two? E.g. total or margin! Thanks for any help. Hi Joe, As far as I am aware there is no support for calculated fields. It would be possible to trigger an OnChange event, but in order to do so, you would need to link it to a picklist (combo box) on the form. This could then perform a calculation and assign it to the field that you want. Pretty ordinary solution, I know... but you work with wha...

How do I join 2 fields to limit criteria?
I am trying to set a criteria with an OR clause in between 2 fields. Let's call the 2 fields Field1 and Field2. I'm trying to do this example: Only show the records that have this criteria: Field1 = A, and Field2 = B,C,D,E,F,G These 2 fields contain the same type of data, and we need to limit the criteria together between the 2 fields?? Any ideas on how to do this when using excel to build a query in Microsoft query? Christen You could use an advanced filter. Assuming field1 and field2 refer to columns A and B, type this into an open cell (say M22) outside the data table: ...

Query Criteria 05-24-07
Finding it hard to get my head round this so help needed! I have a query based on form fields. There is date range field (always selected) and 2 other optional criteria needing 4 WHERE/OR statements to get the required results. I want to add another optional criteria field which i think will leave me with 11 WHERE/OR statements and a big headache! Is there a simple way around this? ...

Search doesn't work
I am not sure if this quit working when IT upgraded my Excel version from Excel 2000 to Excel 2002 or not, but it may be. If I highlight a row, and click edit, search, then type in search string I know is in the range I highlighted, and then change the look in to values, and click find next, it keeps being stubborn and saying that Microsoft Excel cannot find the data you're searching for. Check your search options, locations and formatting. There is no location as such. It has choices of Sheet, or workbook, rather than a certain range like what I remember. I think this is a bug...

How to remove Opportunity from the Invoice form?
We will be building Quotes in CRM but fulfilling Orders in Navision. The Invoice will also be created in Navision. We are going to set up a data feed to receive Navision Invoice info into the CRM Invoice form in a read only format. As a result, I need to strip down the Invoice form. For example, the Opportunity should not be on the Invoice form now. I went to remove the attribute but was not able. "The field you are trying to remove is required by the system or business." So I went to the Invoice entity, found the Opportunity attribute, and was hoping it would be set ...

How to add custom field to Task Form in Outlook2k?
I have created custom fields for task and want to add them to the form that pops up when I create a new task. I have added my custom fields to the Simple View & others where task are listed... But I want to be able to input custom data when I create a task... And I want to be able to modify that custom data later if necessary. How can I make my Custom Task Fields appear in the standard Task Form ? thanks for any help. tmb Start by going to http://www.slipstick.com/dev/forms.htm to see about Outlook forms design. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Abso...

Disapearing forms and subforms
I have a front end database connected to tables in a back end. Monthly this FE is used to collect metrics. Today, all my forms and subforms are blank. The backend has retained the information entered into it last month. The date update Query and Macro are pulling in 1/31/2008. Can anyone give me a clue why/how this could happen? I am pulling back up system tapes to do a restore but would like to know how to prevent anything like this happening in the future I have seen this happen when the connection to the backend is not working ( I can't quite recall why it wasn't working). Tr...

"Move Message" Rule not working fully
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C83F07.B735DAA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have created a rule that moves email messages that I receive with = certain criteria. This is the description of the rule as seen in the = Rules Wizard dialogue box: Apply this rule after the message arrives with '@usengineering.com' or '@psicontractors.com' in the sender's = address move it to the USEngineering folder The problem? The rule is really just making a copy of each m...

Opening a database with a new form
Hi I am creating a database in which when users open it, it defaults to a single form. At the moment, when they open it, it opens on a record. Is it possible to open it so that it's a new form (i.e. record) instead of a previously created record> Thank you in advance for your help. You can set the data entry property of that form to No. It will open ready to add a new record. If you want users to be able to go back to other records, you can use code in the load event to make the form go to a new record (without setting the data entry property to No). DoCmd...

User form Default value in text box
Hi all. Quick question. I have a user form in excel which has a text box which i want to have the value defaulted to the current date. I've looked at several posting on this site but haven't gotten it to work yet. Any ideas on how to set the text box with the current date as a default value? Any help or direction is appreciated. Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201006/1 Hi, Try this way Private Sub UserForm_Activate() TextBox1.Text = Date End Sub -- Mike When competing hypotheses are oth...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

OWA - Form Based Authentication
Hello All: Have OWA setup for form based authentication and works fine. What I have noticed is that it is not dropping a cookie on the clients machine for either private or public selection. I'd really like to do away with this altogether if possible but it looks like not an option. Why would the server no generate the appropriate cookie? Client machine is setup to accept cookies so it looks to be a server side issue. Any help apprecaited. ...

How stop duplicate fields when creating a Report in Access?
no metter what method I use to create the report - it throws double fields into the report. You manually delete one of each and proceed and it continues to add double fields. We did not have this issue in 2003 version. WHat is the problem and how do we fix it? Are you talking about the record source (a query) for the report listing fields twice? If so, check the underlying query and see if it has an asterisk (return all fields) in SELECT clause of the query. It is a property of queries in design view (Output all fields). Set this to No to eliminate the fields showing ...

Menu Maker and Excel 2007
Hi all I see that John Walkenbach's Menu Maker doesn't require much modification to work with the QAT. Is there a similar method for building a custom Ribbon tab or group? Thanks in advance Paul Martin Melbourne, Australia Hi, Check out Mike's menu builder for 2007. http://www.datapigtechnologies.com/freeware.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Paul Martin" <melbournefilm@gmail.com> wrote in message news:40c5f73c-9e78-43f5-b4fd-d3f84097837c@f20g2000vbl.googlegroups.com... > Hi all > &...

Add-in Sign-in has stop working
Recently I purchased a new laptop w/ Windows 7. And needed to switch to using Windows live. In using Windows Live, which is a great program. However, I've noticed that every time I open a Miscrosoft Office Program and the close out of it, I get a message box: Microsoft Office Add-in Sign-in has stopped working I just click close program and everything is OK. How do I fix things so that message does not pop up after using an Office program and closing it? Bill http://office.microsoft.com/en-us/help/HA100341271033.aspx "wmcook" <wmcook@discussions.micro...

Excel 2007 sorting bug?
If an Excel 2007 sheet (#1) has formula referenced cells to another sheet (#2), and if the column in sheet (#2) containing the referenced cells is then A-Z sorted on the value then the original references in sheet #1 get lost as they are now pointing to different cells. Is this an Excel 2007 bug? Why after the sort does the relative position not move so keeping the correct references?. I have not used $ in the cell address. Beemer No, this is not a bug. If you have cells on Sheet1 pointing to other cells on Sheet2 with a direct reference like =Sheet2!B1, then if you change the c...

Drag Email into Access Form
As a way of monortoing Email useage i would like to be able to drag an email from Outlook into a memo field on an access form. Currently it only drags the header, how do i get the body of the email message dragged across as well? ...

Microsoft Access Tables and Forms
I have made up a table and then created a form from it. The table does not reflect the changes made in the form. I have done everything that I can think of in order to make this work (input info, go to next record, close form), but table still will not update. I want to create reports, but I have no totals showing up, because they will not show in the table. Desperately in need of HELP!!! Helen Helen In a standard Access design, you create a table, (optionally, you create a query based on the table), you create a form bound to the table (optionally, to the query). When you open the for...

Passing Query Parameters from BP to a URL
Hey Eighties Fans: After constructing a query in Business Portal, is it possible to pass a parameter from that query into a URL? Doing so would be similar to the SmartList Builder feature where you can create a web site (URL) "Go-To" link and pass a parameter within that link that literally becomes part of the web site address that appears in your web browser. Thanks! childofthe1980s ...

working minimized
can anyone tell me if there's a way to close the outlook and have it run minimized on my notification area (area where the clock and volume control is located) instead of my taskbar. what i'm trying to do is leave my outlook running checking my email account constantly but not have it minimized on my taskbar but only have an icon on the notification area. thank you all! lukas What version of Outlook "Lukas Beltrame" <lukasob@yahoo.com> wrote in message news:072b01c3d413$97a388d0$a301280a@phx.gbl... > can anyone tell me if there's a way to close the outlo...

Task and Calendar links don't work in Outloof today
Just reinstalled Outlook 2000 after going back to internet mail only setup. I had trouble with the task and calendar buttons on my shortcut bar but I deleted them and then added them again and they work. But I cannot hit the task or calendar link in Outlook Today. It just sits there. I can however use the task shortcut button and add tasks which do appear on Outlook Today. I can also check them as done. Anyone have any ideas? ...

software update problem in $2007 H&B
I've installed Money 2007 H&B and my location is China.Then every time I launched the program,it says something like "To operate correctly,click 'ok' to let Money download the software update." But if I click 'OK',after it download autoupdate.dll(.cab) and autoupdate.xml,it alarms "A software update is already in progress.Please try again when the update is complete." How can I solve the problem ? By the way , can i download the software update manually? I'm having the same problem, and i cannot find the update on microsoft's website...

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

sending email in outlook 2007 is not working
sending email in outlook 2007 is not working. My gut tells me it has something to do with the setup. What happens and what does the error message say -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "matt" <matt@discussions.microsoft.com> wrote in message news:A9819497-A904-4827-8A8B-CFFB88E08B16@microsoft.com... > sending email in outlook 2007 is not working. My gut tells me it has > something to do with the setup. > ...