Problem passing a date

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

Guest

Hello

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

PostBack = "UPDATE Contracts SET Contracts.due = " &
Format(PreviousBillingDate([frequency],SelectedDate), "\#mm\/dd\/yyyy\#")) &
_ & " 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
 
What type of data does PreviousBillingDate() return?

It should work if you used:
Function PreviousBillingDate(..., ...) As Date
but of you set it up to return a Variant try:

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

It returns a data i.e.

Public Function PreviousBillingDate(f As Byte, d As Date) As Date

Select Case f

Case Is = 1

'MsgBox "I've got " & f & " - " & d 'invoice billing frequency and
date

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


I've tried numerous combinations of Date and Variant. The above message box
is for debugging so I can see what date as accepted by the function. The
date arrives as mm/dd when the DoCmd Run SQL str explicitely sends it as dd/mm

I'd really like to get it working because it's part of a neat and timesaving
feature for rolling back an invoice...

TIA, Simon


TIA, Simon

Allen Browne said:
What type of data does PreviousBillingDate() return?

It should work if you used:
Function PreviousBillingDate(..., ...) As Date
but of you set it up to return a Variant try:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SimonW said:
Hello

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

PostBack = "UPDATE Contracts SET Contracts.due = " &
Format(PreviousBillingDate([frequency],SelectedDate), "\#mm\/dd\/yyyy\#"))
&
_ & " 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...
 
Your original description of the problem suggested that the issue occurs
when the date could be ambiguous (i.e. the day number is up to 12.) This
suggests that there is a point at which Access is misunderstanding the date,
i.e. it is treating it as a string at some point. How about SelectedDate? Is
that a Date/Time type field? Has PreviousBillingDate defined this argument
as a Date type also?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SimonW said:
Hi

It returns a data i.e.

Public Function PreviousBillingDate(f As Byte, d As Date) As Date

Select Case f

Case Is = 1

'MsgBox "I've got " & f & " - " & d 'invoice billing frequency and
date

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


I've tried numerous combinations of Date and Variant. The above message
box
is for debugging so I can see what date as accepted by the function. The
date arrives as mm/dd when the DoCmd Run SQL str explicitely sends it as
dd/mm

I'd really like to get it working because it's part of a neat and
timesaving
feature for rolling back an invoice...

TIA, Simon


TIA, Simon

Allen Browne said:
What type of data does PreviousBillingDate() return?

It should work if you used:
Function PreviousBillingDate(..., ...) As Date
but of you set it up to return a Variant try:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SimonW said:
Hello

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

PostBack = "UPDATE Contracts SET Contracts.due = " &
Format(PreviousBillingDate([frequency],SelectedDate),
"\#mm\/dd\/yyyy\#"))
&
_ & " 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...
Click to expand...
 
Thanks Allen

The following works brilliantly

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

Thank you for your invaluable guidance

Rgrds, Simon

Allen Browne said:
Your original description of the problem suggested that the issue occurs
when the date could be ambiguous (i.e. the day number is up to 12.) This
suggests that there is a point at which Access is misunderstanding the date,
i.e. it is treating it as a string at some point. How about SelectedDate? Is
that a Date/Time type field? Has PreviousBillingDate defined this argument
as a Date type also?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SimonW said:
Hi

It returns a data i.e.

Public Function PreviousBillingDate(f As Byte, d As Date) As Date

Select Case f

Case Is = 1

'MsgBox "I've got " & f & " - " & d 'invoice billing frequency and
date

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


I've tried numerous combinations of Date and Variant. The above message
box
is for debugging so I can see what date as accepted by the function. The
date arrives as mm/dd when the DoCmd Run SQL str explicitely sends it as
dd/mm

I'd really like to get it working because it's part of a neat and
timesaving
feature for rolling back an invoice...

TIA, Simon


TIA, Simon

Allen Browne said:
What type of data does PreviousBillingDate() return?

It should work if you used:
Function PreviousBillingDate(..., ...) As Date
but of you set it up to return a Variant try:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

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

PostBack = "UPDATE Contracts SET Contracts.due = " &
Format(PreviousBillingDate([frequency],SelectedDate),
"\#mm\/dd\/yyyy\#"))
&
_ & " 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...
Click to expand...
 
Back
Top