By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

Is this syntax correct in a query criteria??

P: n/a
Hey ,

I have built a query which takes values from unbounded fields on a
form, and it all works except for one thing. I have a few fields in
my query that are dates. I also have a start and end date and time on
my form. So, a user will specify the start and end date, and also,
the user has the option of checking which of the time fields from the
query will have these dates as criteria.

So, one of the date fields in my query is called TimeEntered. In the
criteria of my query, I am trying to add the following expression:

IIf([Forms]![FRMreport]![Enter]=Yes, >=
[Forms]![FRMreport]![Start]+[Forms]![FRMreport]![StartTime] And
<[Forms]![FRMreport]![End]+[Forms]![FRMreport]![EndTime],>= #1/1/1998#
And <#1/1/2050#)

Basically I am saying that if the check box is checked, then the query
uses the start and end date that was input as criteria, else it uses
the 1/1/98 and 2050(just default dates to capture all data).

This is not working at the moment. I ideally thought I could use the
above code for every date field and that would work. What is wrong
with my syntax, or if not possible, how can I do this??

THanks,

matt
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Matthew wrote:
Hey ,

I have built a query which takes values from unbounded fields on a
form, and it all works except for one thing. I have a few fields in
my query that are dates. I also have a start and end date and time on
my form. So, a user will specify the start and end date, and also,
the user has the option of checking which of the time fields from the
query will have these dates as criteria.

So, one of the date fields in my query is called TimeEntered. In the
criteria of my query, I am trying to add the following expression:

IIf([Forms]![FRMreport]![Enter]=Yes, >=
[Forms]![FRMreport]![Start]+[Forms]![FRMreport]![StartTime] And
<[Forms]![FRMreport]![End]+[Forms]![FRMreport]![EndTime],>= #1/1/1998#
And <#1/1/2050#)

Basically I am saying that if the check box is checked, then the query
uses the start and end date that was input as criteria, else it uses
the 1/1/98 and 2050(just default dates to capture all data).

This is not working at the moment. I ideally thought I could use the
above code for every date field and that would work. What is wrong
with my syntax, or if not possible, how can I do this??

THanks,

matt


No.

You want something like, use [Forms]![FRMreport]![Enter] as a field in
the query (no need to display, just as criteria), in the Criteria for
that enter "Yes" (or -1 if "Yes" don't work) and the first bit of your
date criteria under the date column. Then on the next line in the grid
(as an "or"), enter "No" (or 0) under the [Forms]![FRMreport]![Enter]
column and the second bit of your date criteria under the date column.
--
This sig left intentionally blank
Nov 13 '05 #2

P: n/a
Matthew Miclea wrote:
I did exactly what you recommended and it worked perfectly. However, if
now I have two checkboxes, which control two different Time fields in
the query, there will be four possible scenarios(four rows of criteria),
if I have 3 checkboxes, there are now 8 possible scenarios, and so on.
In my case, there are 9 different scenarios(at the very best I can cut
that number down to 6) which means that I will have from 64 to 512
different scenarios! In the query design view, it looks like there are
only 9 criteria rows. There has to be a way to code this in VB in a
more efficient manner. Now, I understand code a little, so I could
probably figure out the logic for this, but where exactly to I write
this?? I'm not familiar with writing queries anywhere other than in the
query design view. Thanks,


You can place the code behind the button that opens the report, e.g.

Sub MyButton_Click()
Dim strCriteria as string
if MycheckBox1.Value then
strCriteria="Date1 < blah"
else
strCriteria = "Date1 > blah"
end if
if MyCheckBox2.Value then
strCriteria=strCriteria & "Date2 < blah"
else
strCriteria=strCriteria & "Date2 > blah"
end if
docmd.OpenReport "MyReport",acViewPreview,,strCriteria
End Sub

--
This sig left intentionally blank
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.