katlee
Last Activity: Jul 29 '10, 03:35 PM
Joined: Jul 12 '10
Location:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessWow works like a charm!! Many, many thanks!!! -
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessI realized that what the three records have in common is they have values in all the other fields being queried (none of the fields were left blank in the table)... the problem is that some records will not have values in a certain field because the information is unknown. For example, very old photographs were assigned Photo IDs and in a certain year this stopped being done. I want users to be able to search by the ID if they know it, but if they...Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessOk, makes sense!! My SQL is currently
...Code:WHERE (([ID] Like Nz([Forms]![Search]![txtPhotoID],"*")) AND ([Project] Like Nz([Forms]![Search]![txtProject],"*")) AND ([Date] Between Nz([Forms]![Search]![txtStart],#1/1/1900#) And Nz([Forms]![Search]![txtEnd],#31/12/9999#)) AND ([Description] Like "*" & Nz([Forms]![Search]![txtDescription],"*") & "*"))
Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessSorry
To be more specific, I have a dummy set of records right now that I am using to see if the query will work. Currently, records have either "CA" or "AZ" for the state (when the database is...Code:AND ([Region] Like Nz ([Forms]![Search]![cboRegion],"*") AND ([AreaOffice] Like Nz ([Forms]![Search]![cboAreaOffice],"*") AND ([State] Like Nz ([Forms]![Search]![cboState],"*")
Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessSpoke a little too soon.. I have three combo boxes (cboState, cboAreaOffice, cboRegion) that have queries of my master table as their source..
... is what I am using now and I get the expected results when selected one state but not the other. Do I need to do something different?Code:AND ([Region] Like Nz ([Forms]![Search]![cboRegion],"*") AND ([AreaOffice] Like Nz ([Forms]![Search]![cboAreaOffice],"*")
Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessThe query seems to be working how it is supposed to! Hopefully I can apply this method to include all the fields.. Thanks to colintis for your patience and Neopa for breaking it down into simpler terms for such a beginner! What a valuable resource this forum is :)Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessI tried removing "*" and the query still didn't return any results.
I thought there may be a way to do it using VBA but I have never done anything with VBA and am a little intimidated. Would I write this code in the "On Click" event of a button on the form where the users enter the parameters?
Right now I have a form thats record source is the query and am using a macro to get it to pop up and display...Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessAlso I tried
as a test because there was no syntax error and the query returned zero results when entering just one parameter and zero results when entering both parameters.Code:WHERE ((([Historical Photos].Project) Like '" & Nz([Forms]![Search]![txtProject],"*") & "') AND (([Historical Photos].Division) Like '" & Nz([Forms]![Search]![txtDivision],"*") & "'));
Leave a comment:
-
katlee replied to Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessI used what you gave me and this error message "Syntax error (missing operator) in query expression" came up and it highlighted LIKE.Leave a comment:
-
katlee started a topic Multiple parameter query; ignore blanks & have results match all parameters enteredin AccessMultiple parameter query; ignore blanks & have results match all parameters entered
I am using Access 2007 to design a database that will allow users to search an inventory of historical photographs. Currently I have a table with fields such as Date, ID, Description, Project, Division, Unit, Facility, Feature, Subfeature, Region, Area Office, and State. I am using a query by form method that have either text boxes or combo boxes for each of the fields. I want users to be able to enter in the fields that they want to search by...Last edited by NeoPa; Jul 15 '10, 04:26 PM. Reason: I tidied up your SQL so it could be read. Please use the [CODE] tags provided.
No activity results to display
Leave a comment: