Problems with mm/dd/yy date format...

[patjones]

Hi:

I have a table that contains several date fields. I set the data type of those fields to Date/Time, and the format property (in table design view) to "mm/dd/yy".

Likewise, in the form that I use to populate those fields, I have corresponding text boxes with the format set to "mm/dd/yy" and input mask 00/00/00;0;*

When I enter dates in all of the date text boxes and save the record, everything is fine. But if I leave a particular date text box blank (which is valid for the particular purpose I'm making the application for) - I get a data type conversion error upon trying to save the record. The VB code gets stuck on the line that attempts to save the blank date to the table (which I discern by hitting "debug" when the error dialogue pops up).

What's going on here? Thanks so much for any insight!

Pat

[FishVal]

Hi:

I have a table that contains several date fields. I set the data type of those fields to Date/Time, and the format property (in table design view) to "mm/dd/yy".

Likewise, in the form that I use to populate those fields, I have corresponding text boxes with the format set to "mm/dd/yy" and input mask 00/00/00;0;*

When I enter dates in all of the date text boxes and save the record, everything is fine. But if I leave a particular date text box blank (which is valid for the particular purpose I'm making the application for) - I get a data type conversion error upon trying to save the record. The VB code gets stuck on the line that attempts to save the blank date to the table (which I discern by hitting "debug" when the error dialogue pops up).

What's going on here? Thanks so much for any insight!

Pat

Insight #1.

Would you be so kind to post the code causing fault.

[garethfx]

Something the darkest valuts of my mind tells me that I had this issue sometime agao. I had it when tabbing through the fields. When leaving a blank the jet still saw a code in the empty textbox.

I have since used a standard impossible date to be remove the Null situation and also to remove any issues like the one described. The date used as default is 31/12/9999. This allows for <> etc

may be wise to look at how you are focusing and exiting the field !

Gareth

[anne25350]

Do you really need the input mask on a date entry? For dates I set the Table field Format to Short Date and the Form field Format to dd/mm/yy no quote marks. I have had no problems with people entering the wrong data as Date fields are so common, they can even enter just 2/7 and the field will populate with 02/07/07.

I hope this helps but I have not been using Access to its fullest for long, and apologise in advance if I have misunderstood your question.

[patjones]

Hi:

Thank you for your responses. Here's an example of code...

!fldDateOfNoPayBegin = Me!txtAddDateOfNoPayBegin

fldDateOfNoPayBegin is the field in the table that is assigned Date/Time type and formatted as mm/dd/yy.

txtAddDateOfNoPayBegin is the text box on the form that is formatted as mm/dd/yy.

So, if I leave the text box empty, I get the data type conversion error. I tried using the Nz function in various ways to solve the issue:

!fldDateOfNoPayBegin = Nz(Me!txtAddDateOfNoPayBegin, Null)

...but this does not help matters. What else can I try out?

Thank you!

Pat

[patjones]

Hi,

I found a solution by using the "IIf" statement...for example:

!fldDateOfBalUsageBegin = IIf(Me!txtAddDateOfBalUsageBegin = "", Null, Me!txtAddDateOfBalUsageBegin)

This seems to be because the IIf statement is more type-friendly than the Nz statement, which didn't work at all.

Anyway, just wanted to point this out so that people with the same or similar problems could get some insight. Thanks!

Pat