leading zeor in 2 digit date format

[buratti]

I have 2 fields on a form that I need to display month and year of a Credit card expiration date. I currently have the fields as number format. I need the fields to be displayed (and also stored if there is a difference) in the format of MM and YY, meaning the leading zeors in January (01), or the year 2009 (09) are displayed. I tried using a custom input mask; did not work, then I tried the format function and also didn't work.

Private Sub ExpireYR_AfterUpdate()
Me.ExpireYR = Format(Me.ExpireYR, "0000")
End Sub

Changing the field to a text format DID work, but I cannot have that, due to a mismatch in field types in an update query needed later on.

Any sugestions, or am I using the format function wrong?

 

[gemma-the-husky]

dont do that - store the date in a date field

just display it when you need it as follows

format(thedate,"MMYY")

 

[buratti]

I tried that in the beginning, but it caused different problems. First of all, I have 2 separate fields; one for the month and one for the year. (both needed in 2 digit format) When I enter a date in the year field of, lets sat 09, with the field set to date, I now get an error "The value you entered isn't valid for this field", and for all existing fields marked 9 (no leading zero's) it displays the date 1/8/1900. Thanks, but any other suggestion or am I just missing something in your answer?

 

[Galaxiom]

Rather than using the function to convert the date to text, set the format property of the controls where it displays on forms and reports as mm/yy. This keeps the data as a date but displays mm/yy.

Similarly in the table you can set the format property of the field.

However when entering a value on a form once the control is in focus the date will display in your local format and demands to be entered in full. Consequently it is better to use two separate controls for inputting month and year.

I would use combos so the user can select the entry. The row source of the month is obvious. The year can use a value list created in VBA from an expression based on the current date and the month box value so that expired dates are not offered.

However still store the data as a date. Create a textbox bound to the expiry date field in your table. Set its format as mm/yy. Lock and disable it. Or make it invisible if you wish.

In the After Update event procedure of the txtYear and txtMonth boxes enter the command:

txtExpiry = DateSerial(txtYear, txtMonth, 1)

 

[vbaInet]

How are the dates gotten? From a calendar control or you're typing it in, or otherwise?

 

[buratti]

I am assuming that you didn't see my last post due to referencing your post times, but I'll repeat.
Galiaxomathome: First, I really don't care much about the "display" of the field, but need the actual value of it to be "01, 02, 03..." due to data matching later on in an update query. Second, the expire field are indeed already 2 separate fields. I tried using combo boxes, and they remove the leading zero's also. Forth, unless I am missing something, there is no option for a MM/YY format. My options are, General Date, Long Date, Medium Date, Short Date, Long Time... No MM/YY format. If you did catch my second post, maybe then explain a little more in detail please.

VBAInet: I am typing the values in manually

 

[vbaInet]

In your table, put the format 00 in each of the field's Format property. Then for validation on the form, check that the number of digits is not more than 2 before saving.

 

[buratti]

oops, my bad! I didn't realize that the format property was NOT bound to just the dropdown options. I put 00 in the property and now it "displays" correctly, BUT like I stated before, I need the actual value to include the leading zero.

Further explination of why:
I am using the QOBDC Driver to link my Access DB to my company Quickbooks file. My access form is to update customer info including credit card data. I use an update query later on to update the table in Quickbooks, but the fields in Quickbooks are very strict as to data types and specific value, ie. credit card expiration year field needs to be rather YYYY format or YY format, nothing else, meaning 09 would work, but 9 would throw up an error and not update correctly. Any further suggestion on how to dave the leading zero in the actual value of the field?
BTW thanks for previous suggestions. they worked, but like stated before not exactly what I need.

 

[vbaInet]

I guess you would always have to ensure it's in that format everytime you are calling the value. This might help:

IIF(Len(myMonth & "") = 1, "0" & myMonth, myMonth)

You won't be able to store leading zeros as a number, only as string.

 

[gemma-the-husky]

i thought that afterwards - normally a credit card date is just given as MM/YY

you could just store these as numbers in two different fields then as you were proposing, and display them as

format(cardmonth,"00") & format(cardyear,"00")

that should work

 

[buratti]

I guess you would always have to ensure it's in that format everytime you are calling the value. This might help:

IIF(Len(myMonth & "") = 1, "0" & myMonth, myMonth)

You won't be able to store leading zeros as a number, only as string.

Thanks,
Well this works, sort of... I put this code in where I am sending the value in the update event and it is sending the leading zero, but I'm still getting an update error.
This particular question is officially solved, but now I have to figure out why I am getting the update error. I can live with it though. The leading zero in the year field is whats giving me the problem, and since its now 2010 I wont have to worry about an 09, 08, etc format for another hunderd years, I'll be ok.
Thanks for your help.

 

[vbaInet]

Glad to know it's working (to an extent). I did mention that you can't store leading zeros in an integer field. So for comparison you use the code above, when it comes to updating you take out the leading zero if it exists by converting to a numeric value. Lookup the Val or CInt functions.

 

[DCrake]

No one has seemed to ask what format the two fields are set as in your table definition for the month and year. If you want to store them with leading zeros then they must be set as text fields, not numbers or dates.

The combos can be value lists with months and years. Even using two columns each the months being "January";"01","February";"02";etc and the years being "2010";"10","2011","11";etc.

And by making the second columns the bound columns then the user interface is more appealing and pleasing to the eye and more user friendly.

As an addon to anyone reading this post there is a sample mdb I posted that validates 16 digit card numbers and also validates bank sort codes for the UK.


David

 

[vbaInet]

Hehe!! DCrake, the OP did actually say in the first post and leading on from that I suggested that (in post #7) the OP formats the fields in the tables to 00. Galaxiom also suggested using a text field. So all these things were covered, but that wasn't what the OP wanted.

Your sample db sounds interesting. I haven't had anything to do with account number or credit card number validation so might have a look. Where can I find it?

 

[DCrake]

As the post suggested have a look in the sample databases forum, but if you can't stretch to that here is the Link.

David

 

[vbaInet]

Great, thanks David.

 

[buratti]

Thanks for the continuing replies! I do already have a credit card validate function in my db, but thanks again for the help. I found one a while ago through google.