If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#12
|
|||
|
|||
if statements in queries
I got an invalid use of '.', "!", or () in a query expression. I saw
that there was a left over parenthesis after [Tag]) which I removed. It seems as if this would be easier. If I had 2 fields on the first form and the user left one blank, it seems it would be easy to make a query to use the information provided to return a result. Perhaps a filter is a better choice? On Jun 23, 6:27*pm, "Ken Snell \(MVP\)" wrote: Try this: SELECT Computer_Assignments.* FROM Computer_Assignments WHERE (Computer_Assignments.[First name]=[Forms]! [Frm_Computer_Search]![First Name] OR [Forms]! [Frm_Computer_Search]![First Name] Is Null) AND (Computer_Assignments.[Last Name]=[Forms]![Frm_Computer_Search]![Last Name] OR [Forms]![Frm_Computer_Search]![Last Name] Is Null) AND (Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]! [Serial] OR [Forms]![Frm_Computer_Search]! [Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]! [Frm_Computer_Search]![Tag] OR [Forms]! [Frm_Computer_Search]![Tag] Is Null); -- * * * * Ken Snell MS ACCESS MVP wrote in message ... I'm only querying the second form. *the first form only provides the information to query. *I haven't dealt with filter strings perhaps that is easier. I open the second form using an OpenForm macro. from the search form. SELECT Computer_Assignments.* FROM Computer_Assignments WHERE (((Computer_Assignments.[First name])=[Forms]! [Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR (((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]! [Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]! [Frm_Computer_Search]![Tag])); On Jun 20, 8:36 pm, "Ken Snell \(MVP\)" wrote: How are you "querying" the second form? Are you trying to open that form via DoCmd.OpenForm, and give it a filtering string (fourth argument of the OpenForm method)? Or doing something else? -- Ken Snell MS ACCESS MVP wrote in message .... I am using fields on an initial form to query a second form. However, the query is not working. The two fields are first name and last name. If both first name and last name are entered, it works fine, but if just first name is entered, it produces no results no matter what. Any help is appreciated. Thank in advance. FIRST NAME IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*", [Forms]![Frm_Computer_Search]![FIRST Name]) LAST NAME IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]! [Frm_Computer_Search]![Last Name])- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
|
|||
|
|||
if statements in queries
I don't use Filters very much in my forms; preferring instead to change the
SQL statement of the form's RecordSource query so that it filters within the query itself. Although it may be a bit more detailed than you seek, I have a sample database here that shows how to build SQL queries based on various controls on a form: http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm -- Ken Snell MS ACCESS MVP wrote in message ... I got an invalid use of '.', "!", or () in a query expression. I saw that there was a left over parenthesis after [Tag]) which I removed. It seems as if this would be easier. If I had 2 fields on the first form and the user left one blank, it seems it would be easy to make a query to use the information provided to return a result. Perhaps a filter is a better choice? On Jun 23, 6:27 pm, "Ken Snell \(MVP\)" wrote: Try this: SELECT Computer_Assignments.* FROM Computer_Assignments WHERE (Computer_Assignments.[First name]=[Forms]! [Frm_Computer_Search]![First Name] OR [Forms]! [Frm_Computer_Search]![First Name] Is Null) AND (Computer_Assignments.[Last Name]=[Forms]![Frm_Computer_Search]![Last Name] OR [Forms]![Frm_Computer_Search]![Last Name] Is Null) AND (Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]! [Serial] OR [Forms]![Frm_Computer_Search]! [Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]! [Frm_Computer_Search]![Tag] OR [Forms]! [Frm_Computer_Search]![Tag] Is Null); -- Ken Snell MS ACCESS MVP wrote in message ... I'm only querying the second form. the first form only provides the information to query. I haven't dealt with filter strings perhaps that is easier. I open the second form using an OpenForm macro. from the search form. SELECT Computer_Assignments.* FROM Computer_Assignments WHERE (((Computer_Assignments.[First name])=[Forms]! [Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR (((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]! [Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]! [Frm_Computer_Search]![Tag])); On Jun 20, 8:36 pm, "Ken Snell \(MVP\)" wrote: How are you "querying" the second form? Are you trying to open that form via DoCmd.OpenForm, and give it a filtering string (fourth argument of the OpenForm method)? Or doing something else? -- Ken Snell MS ACCESS MVP wrote in message ... I am using fields on an initial form to query a second form. However, the query is not working. The two fields are first name and last name. If both first name and last name are entered, it works fine, but if just first name is entered, it produces no results no matter what. Any help is appreciated. Thank in advance. FIRST NAME IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*", [Forms]![Frm_Computer_Search]![FIRST Name]) LAST NAME IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]! [Frm_Computer_Search]![Last Name])- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
|
|||
|
|||
if statements in queries
I ended up making an exact copy of the edit screen with all the fields
and changing it so that it couldn't be edited, added to, or deleted from. I then added a button to call up the edit screen. That way the user could search to his hearts content and then when he found the record he wanted, he could hit the edit button to call up the edit screen On Jun 26, 4:57*pm, "Ken Snell \(MVP\)" wrote: I don't use Filters very much in my forms; preferring instead to change the SQL statement of the form's RecordSource query so that it filters within the query itself. Although it may be a bit more detailed than you seek, I have a sample database here that shows how to build SQL queries based on various controls on a form:http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm -- * * * * Ken Snell MS ACCESS MVP wrote in message ... I got an invalid use of '.', "!", or () in a query expression. *I saw that there was a left over parenthesis after [Tag]) which I removed. It seems as if this would be easier. *If I had 2 fields on the first form and the user left one blank, it seems it would be easy to make a query to use the information provided to return a result. *Perhaps a filter is a better choice? On Jun 23, 6:27 pm, "Ken Snell \(MVP\)" wrote: Try this: SELECT Computer_Assignments.* FROM Computer_Assignments WHERE (Computer_Assignments.[First name]=[Forms]! [Frm_Computer_Search]![First Name] OR [Forms]! [Frm_Computer_Search]![First Name] Is Null) AND (Computer_Assignments.[Last Name]=[Forms]![Frm_Computer_Search]![Last Name] OR [Forms]![Frm_Computer_Search]![Last Name] Is Null) AND (Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]! [Serial] OR [Forms]![Frm_Computer_Search]! [Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]! [Frm_Computer_Search]![Tag] OR [Forms]! [Frm_Computer_Search]![Tag] Is Null); -- Ken Snell MS ACCESS MVP wrote in message .... I'm only querying the second form. the first form only provides the information to query. I haven't dealt with filter strings perhaps that is easier. I open the second form using an OpenForm macro. from the search form. SELECT Computer_Assignments.* FROM Computer_Assignments WHERE (((Computer_Assignments.[First name])=[Forms]! [Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR (((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]! [Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]! [Frm_Computer_Search]![Tag])); On Jun 20, 8:36 pm, "Ken Snell \(MVP\)" wrote: How are you "querying" the second form? Are you trying to open that form via DoCmd.OpenForm, and give it a filtering string (fourth argument of the OpenForm method)? Or doing something else? -- Ken Snell MS ACCESS MVP wrote in message .... I am using fields on an initial form to query a second form. However, the query is not working. The two fields are first name and last name. If both first name and last name are entered, it works fine, but if just first name is entered, it produces no results no matter what. Any help is appreciated. Thank in advance. FIRST NAME IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*", [Forms]![Frm_Computer_Search]![FIRST Name]) LAST NAME IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]! [Frm_Computer_Search]![Last Name])- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#15
|
|||
|
|||
if statements in queries
Good going sandpking. Glad you found your solution!
Bonnie http://www.dataplus-svc.com wrote: I ended up making an exact copy of the edit screen with all the fields and changing it so that it couldn't be edited, added to, or deleted from. I then added a button to call up the edit screen. That way the user could search to his hearts content and then when he found the record he wanted, he could hit the edit button to call up the edit screen On Jun 26, 4:57Â*pm, "Ken Snell \(MVP\)" wrote: I don't use Filters very much in my forms; preferring instead to change the SQL statement of the form's RecordSource query so that it filters within the [quoted text clipped - 101 lines] - Show quoted text - -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
|
Thread Tools | |
Display Modes | |
|
|