Between inside IIf on a date query

[maella]

Hi, I would like to make a query that shows the results of all the dates
(option 1 of a option group) and a date range (option 2 with two attached
text boxes in the form). I've tryed this criteria, but it doesn't works:

IIf([Forms]![frmSelect Query]![Frame54]=1;[Date];([tblSales].[Date])
Between[Forms]![frmSelect Query]![Start] Y [Forms]![frmSelect]![End])

Is it possible to do it this way?

Thanks

 

[KARL DEWEY]

Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.

 

[John Spencer]

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[maella]

I'm sorry I forgot to chage the Spanish "Y" for the "and" (I'm from Spain) in
the post, but in the data base I typed well. Now I change the field's names
[Date] by [cDate], but the query still hasn't results. Besides the fields
[cDate],[Start] and [End], all have date format (short date)

Thanks

Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.
--
KARL DEWEY
Build a little - Test a little

Hi, I would like to make a query that shows the results of all the dates
(option 1 of a option group) and a date range (option 2 with two attached
text boxes in the form). I've tryed this criteria, but it doesn't works:

IIf([Forms]![frmSelect Query]![Frame54]=1;[Date];([tblSales].[Date])
Between[Forms]![frmSelect Query]![Start] Y [Forms]![frmSelect]![End])

Is it possible to do it this way?

Thanks

 

[maella]

I'm sorry but it doesn't work, the text boxes [Start] and [End] have short
date format (dd/mm/yyyy, Spain). would I have to change the format to
American style (yyyy/mm/dd) or something similar? I've read something like
that. How would I have to do it?

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.

 

[John Spencer]

Which solution did you attempt to use?

tblSales.[Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
CDate([Forms]![frmSelect Query]![Start])) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; CDate([Forms]![frmSelect]![End]))

IF that does not work, please tell us what you mean by "does not work".
Are you getting a syntax error message?
Are you getting no results?
Are you getting the wrong results?

Date literals in queries should be in the format
yyyy-mm-dd or mm-dd-yyyy

See Allen Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm sorry but it doesn't work, the text boxes [Start] and [End] have short
date format (dd/mm/yyyy, Spain). would I have to change the format to
American style (yyyy/mm/dd) or something similar? I've read something like
that. How would I have to do it?

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.

 

[maella]

I tried the two sentences you gave me and now I've tried the last one with no
results with [Frame54]=1 (all dates) or =2 (date range). Then I added a
calculated field as "ConvertedDate: = CDate([ddDate]) (I changed the date
field's name of tblSales as [ddDate] for avoiding confusion), cut and pasted
the last criteria you gave me and now I have results for the all dates
option, but not for the date range

Which solution did you attempt to use?

tblSales.[Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
CDate([Forms]![frmSelect Query]![Start])) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; CDate([Forms]![frmSelect]![End]))

IF that does not work, please tell us what you mean by "does not work".
Are you getting a syntax error message?
Are you getting no results?
Are you getting the wrong results?

Date literals in queries should be in the format
yyyy-mm-dd or mm-dd-yyyy

See Allen Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm sorry but it doesn't work, the text boxes [Start] and [End] have short
date format (dd/mm/yyyy, Spain). would I have to change the format to
American style (yyyy/mm/dd) or something similar? I've read something like
that. How would I have to do it?

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.

 

[maella]

oops! After saving the query and reloading the [frmSelect Query]! all works
ok! But I have had to add the calculated field to convert the date of
tblSale.[ddDate]

Thanks John,

Which solution did you attempt to use?

tblSales.[Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
CDate([Forms]![frmSelect Query]![Start])) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; CDate([Forms]![frmSelect]![End]))

IF that does not work, please tell us what you mean by "does not work".
Are you getting a syntax error message?
Are you getting no results?
Are you getting the wrong results?

Date literals in queries should be in the format
yyyy-mm-dd or mm-dd-yyyy

See Allen Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm sorry but it doesn't work, the text boxes [Start] and [End] have short
date format (dd/mm/yyyy, Spain). would I have to change the format to
American style (yyyy/mm/dd) or something similar? I've read something like
that. How would I have to do it?

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.

 

[maella]

Could I add another option to the Frame54?, for example search only current
year.

Which solution did you attempt to use?

tblSales.[Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
CDate([Forms]![frmSelect Query]![Start])) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; CDate([Forms]![frmSelect]![End]))

IF that does not work, please tell us what you mean by "does not work".
Are you getting a syntax error message?
Are you getting no results?
Are you getting the wrong results?

Date literals in queries should be in the format
yyyy-mm-dd or mm-dd-yyyy

See Allen Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm sorry but it doesn't work, the text boxes [Start] and [End] have short
date format (dd/mm/yyyy, Spain). would I have to change the format to
American style (yyyy/mm/dd) or something similar? I've read something like
that. How would I have to do it?

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.

 

[John Spencer]

If you want to add a third option to search only current year, I would
just use that to set the values of the start and End controls to the
first and last day of the current year.

In the after update event of Frame54 you would have code like

If Me.Frame54 = 3 then
Me.Start = DateSerial(Year(Date()),1,1)
Me.End = DateSerial(Year(Date()),12,31)
End If

Then if you decide you want the current month, you could add that as a
fourth option and add some more the to after update event of Frame54.

If Me.Frame54 = 4 Then
Me.Start = DateSerial(Year(Date()),Month(Date()),1)
Me.End = DateSerial(Year(Date()),Month(Date())+1,0)
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Could I add another option to the Frame54?, for example search only current
year.

Which solution did you attempt to use?

tblSales.[Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
CDate([Forms]![frmSelect Query]![Start])) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; CDate([Forms]![frmSelect]![End]))

IF that does not work, please tell us what you mean by "does not work".
Are you getting a syntax error message?
Are you getting no results?
Are you getting the wrong results?

Date literals in queries should be in the format
yyyy-mm-dd or mm-dd-yyyy

See Allen Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm sorry but it doesn't work, the text boxes [Start] and [End] have short
date format (dd/mm/yyyy, Spain). would I have to change the format to
American style (yyyy/mm/dd) or something similar? I've read something like
that. How would I have to do it?

:

One way if the field you are applying criteria against ALWAYS has a value.

tblSales.{Date] Between IIF([Forms]![frmSelect Query]![Frame54]=1; #100/1/1#;
[Forms]![frmSelect Query]![Start]) AND IIF([Forms]![frmSelect
Query]![Frame54]=1; #3999/12/31#; [Forms]![frmSelect]![End])

If the date field can be null then enter the following into the criteria

[Forms]![frmSelect Query]![Frame54]=1 OR ([tblSales].[Date]
Between [Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Try changing the syntax like this --
IIf([Forms]![frmSelect Query]![Frame54]=1, [Date], ([tblSales].[Date]
Between[Forms]![frmSelect Query]![Start] AND [Forms]![frmSelect]![End])

You should not use Date as a field name due to being a reserved word and may
cause problems.