Cannot reference a form's date text box in a query

[Guest]

In Access 2003, I have a simple Employee form. The form’s underlying Employee
table contains an EmpNo text field and BirthDate date/time field. Both fields
are bound to text box controls on the Employee form. The Employee table
contains only 1 record.

I have a separate query that references the form’s BirthDate text box
control (see below). In the query, the BithDate_v1 and BithDate_v2 fields
both return the same date value. However, BithDate_v3 field returns an
un-readable value.

Does anyone know why the BithDate_v3 field return an un-readable value?

Select tblEmployee.EmpNo,
tblEmployee.BirthDate As BirthDate_v1,
CDate([Forms]![frmEmplyee]![txtBirthDate]) As BirthDate_v2,
[Forms]![frmEmplyee]![txtBirthDate] As BirthDate_v3
From tblEmployee;

PS. On my PC I have Windows XP (SP2), MS Office Pro (SP1), and MS Access 97
(SR2). When I convert the Access 2003 database to Access 97 and then open it
up under Access 97, the problem disappears.

 

[tina]

my first guess would be that BirthDate_v2 shows a date value because the
source is converted to a date with the CDate() function, as
CDate([Forms]![frmEmplyee]![txtBirthDate])

is there some reason why you didn't use the CDate() function on the source
of BirthDate_v3?

hth

 

[Guest]

Thanks for your response Tina.

I have 30+ business applications with similar code that I'm trying to
convert from Access 97 to Access 2003.

The CDate() function wasn't used because I expected the Access 2003 query to
return the value of the referenced form control without the need for a Type
Conversion function. No Type Conversion function is needed in the query when
a referenced form control contains a non-date value. This query also works in
Access 97.

Thanks

Mark

my first guess would be that BirthDate_v2 shows a date value because the
source is converted to a date with the CDate() function, as
CDate([Forms]![frmEmplyee]![txtBirthDate])

is there some reason why you didn't use the CDate() function on the source
of BirthDate_v3?

hth

In Access 2003, I have a simple Employee form. The form's underlying Employee
table contains an EmpNo text field and BirthDate date/time field. Both fields
are bound to text box controls on the Employee form. The Employee table
contains only 1 record.

I have a separate query that references the form's BirthDate text box
control (see below). In the query, the BithDate_v1 and BithDate_v2 fields
both return the same date value. However, BithDate_v3 field returns an
un-readable value.

Does anyone know why the BithDate_v3 field return an un-readable value?

Select tblEmployee.EmpNo,
tblEmployee.BirthDate As BirthDate_v1,
CDate([Forms]![frmEmplyee]![txtBirthDate]) As BirthDate_v2,
[Forms]![frmEmplyee]![txtBirthDate] As BirthDate_v3
From tblEmployee;

PS. On my PC I have Windows XP (SP2), MS Office Pro (SP1), and MS Access 97
(SR2). When I convert the Access 2003 database to Access 97 and then open it
up under Access 97, the problem disappears.