By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,738 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

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

patjones
Expert 100+
P: 931
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
Jun 28 '07 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Jun 30 '07 #2

P: 49
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
Jun 30 '07 #3

P: 2
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.
Jul 1 '07 #4

patjones
Expert 100+
P: 931
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
Jul 2 '07 #5

patjones
Expert 100+
P: 931
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
Jul 6 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.