unbound date field on a form

[Alex]

I am trying to do what I thought was a very simple thing. I have
created a form with a few unbound text fields. I want to pull these
values into a query where some calculations will be done to them.

This seems to work fine until I set the Format property of the text box
to anything other than blank.

The syntax that I am using for the query is:

TopDate: [forms]![frmEdit]![text6]

If I leave the format property blank, this works ok. As soon as I make
it anything other than blank (E.g. "Short Date"), I get nothing in the
query.

Yes, the form is open.
Yes, the fields are spelled correctly (it works when the format is
blank).
Yes, the form has been saved.
No, there aren't any error messages.
No, it does not work with any other format types (Standard, Long Date,
Short Date, etc.)

Any suggestions? I am banging my head against the wall on this one as
I though it would be simple!
Thanks for any help in advance!

Alex Brewer.

[Allen Browne]

The symptoms you describe would suggest that Access is having a problem
understanding the data types correctly.

When you view the query results, does Access display the TopDate field
left-aligned (indicating it is Text) or right-aligned (correct for dates and
numbers)? Try explicitly typecasting your query field as a date, i.e.:
TopDate: CVDate([forms]![frmEdit]![text6])

As a general guide, you might benefit from:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

If you date format is different from the US, see:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alex" <al*********@rocketmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I am trying to do what I thought was a very simple thing. I have
created a form with a few unbound text fields. I want to pull these
values into a query where some calculations will be done to them.

This seems to work fine until I set the Format property of the text box
to anything other than blank.

The syntax that I am using for the query is:

TopDate: [forms]![frmEdit]![text6]

If I leave the format property blank, this works ok. As soon as I make
it anything other than blank (E.g. "Short Date"), I get nothing in the
query.

Yes, the form is open.
Yes, the fields are spelled correctly (it works when the format is
blank).
Yes, the form has been saved.
No, there aren't any error messages.
No, it does not work with any other format types (Standard, Long Date,
Short Date, etc.)

Any suggestions? I am banging my head against the wall on this one as
I though it would be simple!
Thanks for any help in advance!

Alex Brewer.