Dates Problem?

B

Brad

Thanks for taking the time to read my question.

I am having problems with a query.

The query works on my computer (Regional Setting for Date
are dd-MMM-yyyy) but not on another computer (Regional
Settings for Date are M/d/yyyy)

I have changed the query to reflect this difference.

The Exchange Rate Start Date is 1/1/2001
There is no Exchange Rate End date

The Effective Date used on the form are
7/1/2004 - Works
and
7/2/2004 - Does not work

On my computer the query is:

SELECT tblExchangeRate.ExchangeRate,
tblExchangeRate.StartDate, Format(IIf(IsNull
([tblExchangeRate]![EndDate]),Now(),[tblExchangeRate]!
[EndDate]),"dd-mmm-yyyy") AS TheEndDate
FROM tblExchangeRate
WHERE (((tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate])
AND ((Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"dd-mmm-yyyy"))>=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]));

and both dates work

On the other computer the query is:

SELECT tblExchangeRate.ExchangeRate,
tblExchangeRate.StartDate, Format(IIf(IsNull
([tblExchangeRate]![EndDate]),Now(),[tblExchangeRate]!
[EndDate]),"dd-mmm-yyyy") AS TheEndDate
FROM tblExchangeRate
WHERE (((tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate])
AND ((Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"m/d/yyyy"))>=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]));

only 7/1/2004 works

I can't get the "m" in the second query to stay uppercase
("M"), it always changes back to "m".

The strange part is that if you enter a date on the form
of 7/1/2004 the query works, but if you enter a date of
7/2/2004 or later, the query doesn't work.

Any ideas?

Thanks so much.

I'll check back often if anyone has questions.

Brad
 
C

Chris Nebinger

Hey, Brad...

Access stores dates internally as a number representing
the number of days since 1900 (ish). The decimal part of
the number is the hours/minutes/seconds of that time part.

So, formatting the date really shouldn't matter, as any
date (as long as it is correct) SHOULD work.

You where clause shows


tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]

so far so good, you want to see if the startdate is before
the effective date..



Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"dd-mmm-yyyy")>=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]


And if the exchange rate does not have a end date, use
now, otherwise use that end date, and they should be
greater than or = to the Effective Date.


I understand what you're trying to do, but I wonder why
you need the Format function? Would this work?

NZ(tblExchangeRate!EndDate,Now()) >= [Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]


Note, you can select View-SQL to see the actual SQL and
make this change...


Chris Nebinger
-----Original Message-----
Thanks for taking the time to read my question.

I am having problems with a query.

The query works on my computer (Regional Setting for Date
are dd-MMM-yyyy) but not on another computer (Regional
Settings for Date are M/d/yyyy)

I have changed the query to reflect this difference.

The Exchange Rate Start Date is 1/1/2001
There is no Exchange Rate End date

The Effective Date used on the form are
7/1/2004 - Works
and
7/2/2004 - Does not work

On my computer the query is:

SELECT tblExchangeRate.ExchangeRate,
tblExchangeRate.StartDate, Format(IIf(IsNull
([tblExchangeRate]![EndDate]),Now(),[tblExchangeRate]!
[EndDate]),"dd-mmm-yyyy") AS TheEndDate
FROM tblExchangeRate
WHERE (((tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate])
AND ((Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"dd-mmm-yyyy"))>=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]));

and both dates work

On the other computer the query is:

SELECT tblExchangeRate.ExchangeRate,
tblExchangeRate.StartDate, Format(IIf(IsNull
([tblExchangeRate]![EndDate]),Now(),[tblExchangeRate]!
[EndDate]),"dd-mmm-yyyy") AS TheEndDate
FROM tblExchangeRate
WHERE (((tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate])
AND ((Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"m/d/yyyy"))>=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]));

only 7/1/2004 works

I can't get the "m" in the second query to stay uppercase
("M"), it always changes back to "m".

The strange part is that if you enter a date on the form
of 7/1/2004 the query works, but if you enter a date of
7/2/2004 or later, the query doesn't work.

Any ideas?

Thanks so much.

I'll check back often if anyone has questions.

Brad
.
Click to expand...
 
G

Guest

Thanks so much Chris for following up on this. I really
appreciate it.

Even if you do the NZ funciton, won't the Now() portion
still return 7/15/2004 15:41:34

Now() Portion Formatted Now() Portion
7/15/2004 15:44:34 <> 7/15/2004

so that is why I formatted it. So that I could just use
the date part without the time.

NZ will just replace a null End date with 7/15/2004
15:44:34 which still leaves me with my problem

Brad

-----Original Message-----
Hey, Brad...

Access stores dates internally as a number representing
the number of days since 1900 (ish). The decimal part of
the number is the hours/minutes/seconds of that time part.

So, formatting the date really shouldn't matter, as any
date (as long as it is correct) SHOULD work.

You where clause shows


tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]

so far so good, you want to see if the startdate is before
the effective date..



Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"dd-mmm-yyyy")>=[Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]


And if the exchange rate does not have a end date, use
now, otherwise use that end date, and they should be
greater than or = to the Effective Date.


I understand what you're trying to do, but I wonder why
you need the Format function? Would this work?

NZ(tblExchangeRate!EndDate,Now()) >= [Forms]!
[frmtblPricingIngredients]![PricingIngredEffectiveDate]


Note, you can select View-SQL to see the actual SQL and
make this change...


Chris Nebinger
-----Original Message-----
Thanks for taking the time to read my question.

I am having problems with a query.

The query works on my computer (Regional Setting for Date
are dd-MMM-yyyy) but not on another computer (Regional
Settings for Date are M/d/yyyy)

I have changed the query to reflect this difference.

The Exchange Rate Start Date is 1/1/2001
There is no Exchange Rate End date

The Effective Date used on the form are
7/1/2004 - Works
and
7/2/2004 - Does not work

On my computer the query is:

SELECT tblExchangeRate.ExchangeRate,
tblExchangeRate.StartDate, Format(IIf(IsNull
([tblExchangeRate]![EndDate]),Now(),[tblExchangeRate]!
[EndDate]),"dd-mmm-yyyy") AS TheEndDate
FROM tblExchangeRate
WHERE (((tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]! [PricingIngredEffectiveDate])
AND ((Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"dd-mmm-yyyy"))>= [Forms]!
[frmtblPricingIngredients]! [PricingIngredEffectiveDate]));

and both dates work

On the other computer the query is:

SELECT tblExchangeRate.ExchangeRate,
tblExchangeRate.StartDate, Format(IIf(IsNull
([tblExchangeRate]![EndDate]),Now(),[tblExchangeRate]!
[EndDate]),"dd-mmm-yyyy") AS TheEndDate
FROM tblExchangeRate
WHERE (((tblExchangeRate.StartDate)<=[Forms]!
[frmtblPricingIngredients]! [PricingIngredEffectiveDate])
AND ((Format(IIf(IsNull([tblExchangeRate]![EndDate]),Now
(),[tblExchangeRate]![EndDate]),"m/d/yyyy"))>=[Forms]!
[frmtblPricingIngredients]! [PricingIngredEffectiveDate]));

only 7/1/2004 works

I can't get the "m" in the second query to stay uppercase
("M"), it always changes back to "m".

The strange part is that if you enter a date on the form
of 7/1/2004 the query works, but if you enter a date of
7/2/2004 or later, the query doesn't work.

Any ideas?

Thanks so much.

I'll check back often if anyone has questions.

Brad
.
Click to expand...
.
Click to expand...
 
J

John Spencer (MVP)

I think you might want to try something like

SELECT ExchangeRate,
StartDate,
EndDate
FROM tblExchangeRate
WHERE
StartDate<=[Forms]![frmtblPricingIngredients]![PricingIngredEffectiveDate]
AND (EndDate >=[Forms]![frmtblPricingIngredients]![PricingIngredEffectiveDate]
Or EndDate is Null)

ACCESS will reformat that if you switch to query grid design view, but it should
still work.

You can use DateValue function to strip off the time from a date

DateValue(Now()) returns 7/15/2004 with no attached time (well actually it's
exactly midnight).
 

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

Formatting date gives bad results in query 2
2007 to 2003 function problem 1
Changing date format 2
Querying between two dates doesn't work if Start and End are ident 10
Filtering by Month 1
Union query - sort of 4
Date format in query export. 4
Calculation Problem 1

Top