Problem handling a date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi folks

I'm calling a function from inside a DoCmd.RunSQL as follows

PostBack = "UPDATE Contracts SET Contracts.due =
PreviousBillingDate([frequency],#" & SelectedDate & "#)" _
& "WHERE (Contracts.contpk)=" & SelectedCont

The function accepts the parameters and performs a simple task i.e.

PreviousBillingDate = DateAdd("d", -7, d)

Sometimes it works and sometimes it doesn't. When it doesn't work the date
appears in US format i.e. 12/3/2005 when I really mean it to be in UK format
3/12/2005

I cannot understand why the behavior occurs. I have tried accepting the
parameter As Variant and As Date.

Again, sometimes it works. Can anyone help

Fairly urgent please :-)

TIA, Simon
 
You cannot use dd/mm/yyyy format in SQL statements (well, you can, but not
for the first 12 days of each month).

Whenever Access sees nn/nn/nnnn, it will first try mm/dd/yyyy, regardless of
what your Short Date format has been set to through Regional Settings. Only
if that date is impossible (such as when the first two digits are 13 or
higher) will it then switch to dd/mm/yyyy format.

The solution is to explicitly format the date in your SQL statement:

PostBack = "UPDATE Contracts SET Contracts.due = " &
Format(PreviousBillingDate([frequency],SelectedDate), "\#mm\/dd\/yyyy\#")) &
_
& " WHERE (Contracts.contpk)=" & SelectedCont
 
A thousand thanks, Douglas, I'll get back to work now...

Simon

Douglas J Steele said:
You cannot use dd/mm/yyyy format in SQL statements (well, you can, but not
for the first 12 days of each month).

Whenever Access sees nn/nn/nnnn, it will first try mm/dd/yyyy, regardless of
what your Short Date format has been set to through Regional Settings. Only
if that date is impossible (such as when the first two digits are 13 or
higher) will it then switch to dd/mm/yyyy format.

The solution is to explicitly format the date in your SQL statement:

PostBack = "UPDATE Contracts SET Contracts.due = " &
Format(PreviousBillingDate([frequency],SelectedDate), "\#mm\/dd\/yyyy\#")) &
_
& " WHERE (Contracts.contpk)=" & SelectedCont



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SimonW said:
Hi folks

I'm calling a function from inside a DoCmd.RunSQL as follows

PostBack = "UPDATE Contracts SET Contracts.due =
PreviousBillingDate([frequency],#" & SelectedDate & "#)" _
& "WHERE (Contracts.contpk)=" & SelectedCont

The function accepts the parameters and performs a simple task i.e.

PreviousBillingDate = DateAdd("d", -7, d)

Sometimes it works and sometimes it doesn't. When it doesn't work the date
appears in US format i.e. 12/3/2005 when I really mean it to be in UK format
3/12/2005

I cannot understand why the behavior occurs. I have tried accepting the
parameter As Variant and As Date.

Again, sometimes it works. Can anyone help

Fairly urgent please :-)

TIA, Simon
Click to expand...
Click to expand...
 
Back
Top