Feeding query criteria from form text box

[wandan]

Hello,

I have an issue I am trying to resolve, and I am not having much luck.

First a little history...I use text boxes in Access forms to feed criteria in Access queries. For example, the form might ask the user to enter a date then click a command button, which runs a macro that runs several queries. Many or all of those queries (each set up to pull from a different source table) use the same date for it's criteria. This saves a lot of time in opening each query in design view, changing the date, then running it. This process has been primarily used for running periodic reports. Now, we are branching out in to building more interactive tools that our users can use.

Now the issue... My problem occurs when there are multiple criteria options on the form for the user (for example: date, group_name and group_number). I would like for the user to be able to enter only the date if they wish to pull all group_names and group_numbers for that date, or only group_name for all information pertaining to that group_name, etc. Well, if only one item is entered the query returns nothing. I believe the query is picking up the blank text boxes as NULL fields because I entered the following in the criteria field:

IIf(IsNull([Form]![group_name]),"Hello",[Form]![group_name])

This returned the group_name Hello data when the text box was left blank, so obviously it is reading the text box as NULL. I can't figure out what to put in the second part of the iif statement to make it return everything. I tried Is Not Null and in the number field, it doesn't work to use >=100.

I have also played with setting the default value in the form to >=100 or Like"*", but that didn't work either. In fact, that didn't work with entering even a valid number.

Any help will be GREATLY, GREATLY appreciated!!!

 

[ploeryan]

Hello,

I am doing something similar in one of my databases and the syntax I have is as follows using your above example:

IIf(IsNull([Form]![group_name].value),"Hello",[Form]![group_name].value)

It works for me.

Hope this helps.

[Afro2]

 

[telephoto]

I don't have the code handy - it's at work.
I have a database which can be interogated using anything from 1 to 4 or 5 specific criteria.
Dim a variable as variant.
Form an SQL string to represent the basic query and cut off the end of it(after "where&quot store this in quotes for use later on.
When the search button on the form is clicked the code checks each textbox for a value and sets the variable to that value, if there is a value it is added to the end of the SQL string (SqlStr=SqlStr & criteria)if there is no value it steps to the next textbox and repeats the action. Once the last box has been checked then add the end of the Sql string from step 1.
If no options are set in the text boxes then reset SqlStr to the basic string without any "where" criteria in order to return all records.
Run the sql string to return the records selected.

Code could be found if required.

telephoto

 

[jimbo62]

I use multiple criteria on forms and run them under a preview report button's on click event extensively try this....

Dim stDocName As String
stDocName = "Report Name"

Dim sWhere As String
'make sure we have an end date and a start date - They
'are required.
If IsNull(Me!StartDate) = True Then
MsgBox "You must enter a start date", , "L&D Report Generator"
End If

If IsNull(Me!EndDate) = True Then
MsgBox "You must enter an end date.", , "L&D Report Generator"

End If
'place the data collected from the fields into the Query
'criteria
sWhere = &quot;qryLD_Dataq_byVendor.Date >= #&quot; & Me!StartDate & &quot;# and qryLD_Dataq_byVendor.Date <= #&quot; & Me!EndDate & &quot;#&quot;

'Check the forms fields for data and include in criteria
'for Query
If IsNull(Me!Warehouse) = False Then
sWhere = sWhere & &quot; and qryName.WHSE ='&quot; & Me!Warehouse & &quot;'&quot;
End If

If IsNull(Me![Catalog#]) = False Then
sWhere = sWhere & &quot; and qryName.[Catalog #] = '&quot; & Me![Catalog#] & &quot;'&quot;
End If

If IsNull(Me!Account) = False Then
sWhere = sWhere & &quot; and qryName.Account = '&quot; & Me!Account & &quot;'&quot;
End If

If IsNull(Me![RGACode]) = False Then
sWhere = sWhere & &quot; and qryName.[RGA Code] = '&quot; & Me![RGACode] & &quot;'&quot;
End If

If IsNull(Me!DollarAmmount) = False Then
sWhere = sWhere & &quot; and qryName.TotalCost <=- &quot; & Me!DollarAmmount & &quot;&quot;
End If

DoCmd.OpenReport stDocName, acViewPreview, , sWhere

Good Luck this took me a few days to figure out the first time the members here helped me sort it all out.


Jimbo

 

[wandan]

Thanks to all of you for your suggestions, but I can't get any of them to work. I assure you this is due to my own lack of knowledge and expertise, not due to your wonderful suggestions.

I am trying to keep it simple and in the GUI query design view criteria field, so ploeryan's suggestion looked really great. However, it doesn't do anything more than the statement without the .value. What I need it to do is recognize the &quot;Hello&quot; portion as if nothing was entered in the criteria at all if the user enters nothing in that text box on the form. I tried using &quot;Is Not Null&quot; (both with quotes and without), but without quotes pulls nothing back and with quotes gives me an error message:

&quot;This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.&quot;

Unfortunately, telephoto's and jimbo62's suggestions are way over my head. I attempted using a click event to run some of the code in jimbo62's example, but I had no luck.

Thanks again...