If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dates Problem?
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 |
#2
|
|||
|
|||
Dates Problem?
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 . |
#3
|
|||
|
|||
Dates Problem?
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 . . |
#4
|
|||
|
|||
Dates Problem?
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). Brad wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to display the first and last dates in series of dates? | superock | Worksheet Functions | 2 | July 4th, 2004 05:27 PM |
PGP problem | Bob Henson | General Discussion | 0 | June 27th, 2004 11:28 AM |
dates problem | bogi | Running & Setting Up Queries | 2 | June 1st, 2004 06:53 PM |
Update query problem | WWV | Running & Setting Up Queries | 1 | June 1st, 2004 07:12 AM |
Combo Box Problem | Charles | Using Forms | 0 | May 28th, 2004 09:15 PM |