Carry Over Data to Invoice

[J. Trucking]

Hello,

I am having a slight problem. I have a form/table which represents
various projects. This includes the project name, customer, etc. I
have a command button on this form in order to create an invoice for
the project (record) who is currently visible. When the user clicks
'Create an Invoice', a new form pulls up and asks the user what date
range they would like to specify for the invoice (to only collect
those hours worked during the date range). The user enters in a
'StartDate' and an 'EndDate' and then hits 'Okay'. Hitting 'Okay'
causes yet another new form to open (the invoice). To save the user
some time, I have coded the 'OnOpen' section of the Invoice Form to
have the default value of the 'Customer' and 'Project' to be that of
the first form. As well, the values of 'StartDate' and 'EndDate' on
the second form become the default values of the date range on the
invoice. The 'Customer' and 'Project' field are populated perfectly
but the dates keep getting screwed up and I'm not sure what I've
done. The code is as follows:

If CurrentProject.AllForms("frmInvoiceDateSelection").IsLoaded Then
Me![StartDate].DefaultValue = Forms![frmInvoiceDateSelection]!
[StartDate]
Me![EndDate].DefaultValue = Forms![frmInvoiceDateSelection]!
[EndDate]
End If

If I enter in 01/01/08 and 01/31/08 on 'frmInvoiceDateSelection' and
hit okay, the values of StartDate and EndDate on the Invoice both show
up as 12/30/1899. I checked the data type of the Invoice table for
both of the dates and they are both Short Date. This is the same on
the Invoice form. On 'frmInvoiceDateSelection' the StartDate and
EndDate fields are also Short Date with the Input Mask 00/00/00. If I
leave the dates blank, and hit 'Okay' on 'frmInvoiceDateSelection', I
get a Type Mismatch Error. Does anyone know where I could be going
wrong. I think it might be something pretty simple but for the life
of me, I just can't figure it out. Any help would be greatly
appreciated.

Thanks in Advance,

John

 

[Ken Sheridan]

John:

The DefaultValue property is a string expression regardless of the data type
involved, so should always be wrapped in quotes characters. This is
particularly important with dates as a date in short date format will
otherwise be interpreted as an arithmetical expression; 01/01/08 = 0.125.
Because Access implements dates as a 64 bit number starting from 30 December
1899 00:00:00, with the integer part representing days ands the fractional
part the time of day, 0.125 is the value which Access stores for 30 December
1899 03:00:00 (0.125*24 = 3, so its 3 hours after midnight on day zero).

Amend the code to include literal quotes around the values. Literal quotes
within a string already delimited by quotes are represented by a contiguous
pair of quotes characters, so the amended code would be:

If CurrentProject.AllForms("frmInvoiceDateSelection").IsLoaded Then
Me![StartDate].DefaultValue = _
"""" & Forms![frmInvoiceDateSelection]![StartDate] & """"
Me![EndDate].DefaultValue = _
"""" & Forms![frmInvoiceDateSelection]![EndDate] & """"
End If

BTW don't be tempted to use the usual # date delimiter in place of the
quotes. It would actually work in your case as your dates are being entered
in US short date format, but it would give the wrong results on a system
using a different short date format. In my case for instance, using the
European dd/mm/yyyy format, it would change 4 July (which I'd enter as
04/07/2008) to 7 April! This is because date literals must be in US format
or an otherwise internationally unambiguous format.

Ken Sheridan
Stafford, England

Hello,

I am having a slight problem. I have a form/table which represents
various projects. This includes the project name, customer, etc. I
have a command button on this form in order to create an invoice for
the project (record) who is currently visible. When the user clicks
'Create an Invoice', a new form pulls up and asks the user what date
range they would like to specify for the invoice (to only collect
those hours worked during the date range). The user enters in a
'StartDate' and an 'EndDate' and then hits 'Okay'. Hitting 'Okay'
causes yet another new form to open (the invoice). To save the user
some time, I have coded the 'OnOpen' section of the Invoice Form to
have the default value of the 'Customer' and 'Project' to be that of
the first form. As well, the values of 'StartDate' and 'EndDate' on
the second form become the default values of the date range on the
invoice. The 'Customer' and 'Project' field are populated perfectly
but the dates keep getting screwed up and I'm not sure what I've
done. The code is as follows:

If CurrentProject.AllForms("frmInvoiceDateSelection").IsLoaded Then
Me![StartDate].DefaultValue = Forms![frmInvoiceDateSelection]!
[StartDate]
Me![EndDate].DefaultValue = Forms![frmInvoiceDateSelection]!
[EndDate]
End If

If I enter in 01/01/08 and 01/31/08 on 'frmInvoiceDateSelection' and
hit okay, the values of StartDate and EndDate on the Invoice both show
up as 12/30/1899. I checked the data type of the Invoice table for
both of the dates and they are both Short Date. This is the same on
the Invoice form. On 'frmInvoiceDateSelection' the StartDate and
EndDate fields are also Short Date with the Input Mask 00/00/00. If I
leave the dates blank, and hit 'Okay' on 'frmInvoiceDateSelection', I
get a Type Mismatch Error. Does anyone know where I could be going
wrong. I think it might be something pretty simple but for the life
of me, I just can't figure it out. Any help would be greatly
appreciated.

Thanks in Advance,

John

 

[J. Trucking]

Thanks for the help Ken. I'll give it a try.

John