Date Comparison Query

G

Guest

I have the following query:

+30:
IIf([EXPIRE_DATE]<Now()+30,[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

Part 1:
I think that I am putting the time/date comparison together incorrectly (I'm
new to this). I need the EXPIRE DATE compared to a date 30 days from NOW. I
know the comparison is incorrect because when I run the query I get the same
result for all the calculated fields. What is the correct formatting?

Part2:
Is there a way to dynamically label the field with the upcoming months as
opposed to +30 +60 etc. regardless of when the query is performed?

A big THANK YOU for any guidance!
 
G

Guest

part1:
The correct calculation is <dateadd("d", +30, date)
The Now function returns the time, if you are dealing only in dates, you
don't need to bother with Now

part2:
Not using the query builder. If you build your own sql string, the answer
is yes.
One technique is to create two queries. The one you will actually use and
one as a template you can change.

First, read in the SQL of the template
Dim qdf as QueryDef
Dim strSQL as String
Dim strReplace as String

Set qdf = CurrentDb.QuerDefs("MyTemplateQuery")
strSQL = qdf.SQL
Set qdf = Nothing

Now have the SQL in the template set up so that you will have a way to find
and replace each month in the string using the Replace function.

stReplace = "[" & Format(DateAdd("m", 1, date), "mmm") _
& "] As " & Format(DateAdd("m", 1, date), "mmm"))
strSQL = Replace(strSQL, "[Jan] As January", strReplace)

You will need to modfiy this to work with your field names and field headers
and repeat it for each month in your query.

Then save it to the query you actually use:
Set qdf = CurrentDb.QuerDefs("MyProductionQuery")
qdf.SQL = strSQL
Set qdf = Nothing
 
G

Guest

Thanks, but still not working....I should mention that the date is in
YYYYMMDD format.

MH said:
IIf([EXPIRE_DATE]<Now()+30
Click to expand...

I think this should be IIf([EXPIRE_DATE]+30<Now()

MH


Tim Whitley said:
I have the following query:

+30:
IIf([EXPIRE_DATE]<Now()+30,[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

Part 1:
I think that I am putting the time/date comparison together incorrectly
(I'm
new to this). I need the EXPIRE DATE compared to a date 30 days from NOW.
I
know the comparison is incorrect because when I run the query I get the
same
result for all the calculated fields. What is the correct formatting?

Part2:
Is there a way to dynamically label the field with the upcoming months as
opposed to +30 +60 etc. regardless of when the query is performed?

A big THANK YOU for any guidance!
Click to expand...
Click to expand...
 
G

Guest

Does it matter if the Expiration Date is in a YYYYYMMDD format? I am pulling
via ODBC and this is predefined. While I see I was using the wrong
expression to do comparisons, I think the formatting of the Expiration Date
may be a problem as well as my comparison is still not working.

Klatuu said:
part1:
The correct calculation is <dateadd("d", +30, date)
The Now function returns the time, if you are dealing only in dates, you
don't need to bother with Now

part2:
Not using the query builder. If you build your own sql string, the answer
is yes.
One technique is to create two queries. The one you will actually use and
one as a template you can change.

First, read in the SQL of the template
Dim qdf as QueryDef
Dim strSQL as String
Dim strReplace as String

Set qdf = CurrentDb.QuerDefs("MyTemplateQuery")
strSQL = qdf.SQL
Set qdf = Nothing

Now have the SQL in the template set up so that you will have a way to find
and replace each month in the string using the Replace function.

stReplace = "[" & Format(DateAdd("m", 1, date), "mmm") _
& "] As " & Format(DateAdd("m", 1, date), "mmm"))
strSQL = Replace(strSQL, "[Jan] As January", strReplace)

You will need to modfiy this to work with your field names and field headers
and repeat it for each month in your query.

Then save it to the query you actually use:
Set qdf = CurrentDb.QuerDefs("MyProductionQuery")
qdf.SQL = strSQL
Set qdf = Nothing

Tim Whitley said:
I have the following query:

+30:
IIf([EXPIRE_DATE]<Now()+30,[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

Part 1:
I think that I am putting the time/date comparison together incorrectly (I'm
new to this). I need the EXPIRE DATE compared to a date 30 days from NOW. I
know the comparison is incorrect because when I run the query I get the same
result for all the calculated fields. What is the correct formatting?

Part2:
Is there a way to dynamically label the field with the upcoming months as
opposed to +30 +60 etc. regardless of when the query is performed?

A big THANK YOU for any guidance!
Click to expand...
Click to expand...
 
J

John Spencer

If the field you are seeing is exactly that and is never null try
DateValue(Format([TheDateString],"@@@@/@@/@@"))
To convert this to a real date field.

You should also be able to use CDate instead of DateValue if you wanted.

Tim Whitley said:
Does it matter if the Expiration Date is in a YYYYYMMDD format? I am
pulling
via ODBC and this is predefined. While I see I was using the wrong
expression to do comparisons, I think the formatting of the Expiration
Date
may be a problem as well as my comparison is still not working.

Klatuu said:
part1:
The correct calculation is <dateadd("d", +30, date)
The Now function returns the time, if you are dealing only in dates, you
don't need to bother with Now

part2:
Not using the query builder. If you build your own sql string, the
answer
is yes.
One technique is to create two queries. The one you will actually use
and
one as a template you can change.

First, read in the SQL of the template
Dim qdf as QueryDef
Dim strSQL as String
Dim strReplace as String

Set qdf = CurrentDb.QuerDefs("MyTemplateQuery")
strSQL = qdf.SQL
Set qdf = Nothing

Now have the SQL in the template set up so that you will have a way to
find
and replace each month in the string using the Replace function.

stReplace = "[" & Format(DateAdd("m", 1, date), "mmm") _
& "] As " & Format(DateAdd("m", 1, date), "mmm"))
strSQL = Replace(strSQL, "[Jan] As January", strReplace)

You will need to modfiy this to work with your field names and field
headers
and repeat it for each month in your query.

Then save it to the query you actually use:
Set qdf = CurrentDb.QuerDefs("MyProductionQuery")
qdf.SQL = strSQL
Set qdf = Nothing

Tim Whitley said:
I have the following query:

+30:
IIf([EXPIRE_DATE]<Now()+30,[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

Part 1:
I think that I am putting the time/date comparison together incorrectly
(I'm
new to this). I need the EXPIRE DATE compared to a date 30 days from
NOW. I
know the comparison is incorrect because when I run the query I get the
same
result for all the calculated fields. What is the correct formatting?

Part2:
Is there a way to dynamically label the field with the upcoming months
as
opposed to +30 +60 etc. regardless of when the query is performed?

A big THANK YOU for any guidance!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No, that doesn't matter. You will just have to adjust your formatting to
match the format of the data.

Tim Whitley said:
Does it matter if the Expiration Date is in a YYYYYMMDD format? I am pulling
via ODBC and this is predefined. While I see I was using the wrong
expression to do comparisons, I think the formatting of the Expiration Date
may be a problem as well as my comparison is still not working.

Klatuu said:
part1:
The correct calculation is <dateadd("d", +30, date)
The Now function returns the time, if you are dealing only in dates, you
don't need to bother with Now

part2:
Not using the query builder. If you build your own sql string, the answer
is yes.
One technique is to create two queries. The one you will actually use and
one as a template you can change.

First, read in the SQL of the template
Dim qdf as QueryDef
Dim strSQL as String
Dim strReplace as String

Set qdf = CurrentDb.QuerDefs("MyTemplateQuery")
strSQL = qdf.SQL
Set qdf = Nothing

Now have the SQL in the template set up so that you will have a way to find
and replace each month in the string using the Replace function.

stReplace = "[" & Format(DateAdd("m", 1, date), "mmm") _
& "] As " & Format(DateAdd("m", 1, date), "mmm"))
strSQL = Replace(strSQL, "[Jan] As January", strReplace)

You will need to modfiy this to work with your field names and field headers
and repeat it for each month in your query.

Then save it to the query you actually use:
Set qdf = CurrentDb.QuerDefs("MyProductionQuery")
qdf.SQL = strSQL
Set qdf = Nothing

Tim Whitley said:
I have the following query:

+30:
IIf([EXPIRE_DATE]<Now()+30,[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

Part 1:
I think that I am putting the time/date comparison together incorrectly (I'm
new to this). I need the EXPIRE DATE compared to a date 30 days from NOW. I
know the comparison is incorrect because when I run the query I get the same
result for all the calculated fields. What is the correct formatting?

Part2:
Is there a way to dynamically label the field with the upcoming months as
opposed to +30 +60 etc. regardless of when the query is performed?

A big THANK YOU for any guidance!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Projected Date Comparison II 2
Third time's a charm? Date query question 7
IIf function, & Date(), & AND, & > & < 6
Comparison Query 4
Date comparison in IIF statement in a query 1
Date Comparison 5
Pull latest date from Multiple entries 2
Calculated Field in Query - Date Format 5

Top