Syntax Code Help

[Gorphus]

I am having some problems coding criteria for a query. What I am doing is a QBF query to pull information from a form. For this particular field I am wanting to enable the user enter either one or two fields to query by. If the user enters two criteria, I am wanting it to query between the two entries, and if just one is entered I want only that specific entry. Here is what I have so far, please let me know what needs to be changed if this is even possible. I am pretty new to Access and this is probably pretty rough.

"If [Forms]![frmSearch]![txtFrequency2] Is Null Then
[Queries]![qrySearch]![Frequency] = [Forms]![frmSearch]![Frequency1] Or [Forms]![frmSearch]![Frequency1] Is Null Else [Queries]![qrySearch]![Frequency] = Between [Forms]![frmSearch]![txtFrequency1] And [Forms]![frmSearch]![txtFrequency2] Or [Forms]![frmSearch]![txtFrequency1] Is Null"

Thanks in advance,

Jonathan

 

[ToeShot]

Try this:

If [Forms]![frmSearch]![txtFrequency2] Is Null Then
[Queries]![qrySearch]![Frequency] = [Forms]![frmSearch]![Frequency1]
Else [Queries]![qrySearch]![Frequency] = Between [Forms]![frmSearch]![txtFrequency1] And [Forms]![frmSearch]![txtFrequency2]
End If

HTH

 

[Sawatzky]

Dear Jonathan,
in fact I doubt that the value of your formfield ever becomes NULL.

If a textfield on a form is empty it's value is ""

if not ([Forms]![frmSearch]![txtFrequency2]="" and [Forms]![frmSearch]![txtFrequency1]="&quot then
if [Forms]![frmSearch]![txtFrequency2]="" then
[Queries]![qrySearch]![Frequency] = [Forms]![frmSearch]![Frequency1]
else
[Queries]![qrySearch]![Frequency] = "Between " & [Forms]![frmSearch]![txtFrequency1] & " And " & [Forms]![frmSearch]![txtFrequency2]
end if
end if

for me the easiest way to customize the criteria of a query is doing it in access by using the "build..." thing which comes up when you right click in the criteria region of the query in edit-mode.
There you can use all the build-in-functions and reference to the forms and formfields by clicking.
when you open the form and run the query you even can verify if the result is as expected.


HTH

regards Astrid