Query based on form values

[mxp346]

I'm trying to make a form that can be used to query so that the user doesn't have to go in an write up a query in design view. On my form I have text boxes txtBegDate and txtEndDate and AnesNum. I want the form to find all the rows in the table where the field Date is between the two values entered by the user. In my Qry, frmQry, I included several fields including Date. For the criteria of Date in the query design view I included the code

IIf(IsNull([Forms]![frmQry]![txtBeginDate])," " , Between [Forms]![frmQry]![txtBeginDate] And [Forms]![frmQry]![txtEndDate])

What I was trying to do is if the dates are null then i don't want there to be any criteria used in the search but if a beginning date and an ending date are entered then I want them to be used in the criteria.

Any ideas? Am I writing the code wrong or am I going about this the wrong way and should be using a different method?

 

[KyleS]

So if there are no dates then you want them brought up, but if the dates are there then you want the criteria applied right?

(I'll try and make this look like the query biulder, so bear with me)

Field: Date
Criteria: Between [Forms]![frmQry]![txtBeginDate]
And [Forms]![frmQry]![txtEndDate]
Or: Is Null


OR.... if no dates are entered in the form then bring up everything, otherwise limit the results..

In which case you'd be looking for something like this:

Field: Date
Criteria: Between NZ([Forms]![frmQry]![txtBeginDate], #1/1/1801#)
And NZ([Forms]![frmQry]![txtEndDate],#12/31/2999#)

Which if null would give you everything,
Kyle

 

[mxp346]

The second method worked really well. Thank you very much for the helpful and easy to understand reply. If I wanted to do the same thing but with an ID number that is entered, instead of between two dates, how would I do that?

I tried following the same general guidlines but it doesn't work and I'm guessing there's a better way to do it for a specific value. Also out of curiosity, what does the NZ mean?

Field: ProcNum
Criteria:  Between NZ([Forms]![frmQuery]![txtProcNum],"0") And NZ([Forms]![frmQuery]![txtProcNum],"200")

 

[mxp346]

Actually, now that is working. So I guess the only question I have is, is that an efficient way to do it?

 

[KyleS]

The NZ is a function that checks for Null and if so, replaces it with the value you put after the comma. It's the exact same thing as:

IIF(ISNull(Field),"75",Field), but shorter:
NZ(Field,"75&quot

And it is the most effecient way to do it.

Happy to help... Kyle

 

[mxp346]

Thank you very much for the help Kyle

 

[mxp346]

I came across another question if you have time. Is it possible to modify the statement:

Criteria: Between NZ([Forms]![frmQuery]![txtAnesNum],"0") And NZ([Forms]![frmQuery]![txtAnesNum],"200")

so that it adds a wildcard to the end of the Number entered. I've tried a few different ways but I can't get any of them to work.

 

[KyleS]

Not in a between statement. If you add a wildcard then you're not using the "Between" function correctly. What are you trying to do? Kyle

 

[mxp346]

In the field of the table there could be an ID of 121. When I use the statement above the query works. In the same field there could be an entry of 121, 123 which means that 121 and 123 occured. When I use the statement from above the occurrence 121, 123 one does not show up. So I was wondering is it possible to write a criteria so that both the 121 row and the 121, 123 row will appear.

 

[KyleS]

Yes, since the field you're using isn't numeric (Is it?) it can be done with the between. Let me play with it for a minute and I'll post the syntax for you ASAP.

Kyle

 

[KyleS]

As long as the field is text, You could put in

Between nz([forms]![frmQuery]![txtAnesNum],"0&quot & "*" And nz([forms]![frmQuery]![txtAnesNum],"200&quot & "*" Kyle

 

[mxp346]

I got it working. Thank you very much for all the help.