Change date format to numbers?? (1 Viewer)

[Nic]

Hi; is there any way for me to change the format of a date to numbers?
I currently have a textbox with a date in it which appears as "Nov2021'
Can i code a cmd button, which on click, puts this date in plain number format into a second text box as for example 1120201? i.e. MMYYYY??

It is important that it is in number format because of how the data is used after this process.
Any suggestions welcome,
Thanks,

 

[bob fitz]

What is the Format property of the text box

What is the data type of the field in the table that this text box is bound to

 

[theDBguy]

Also, look into the Format() function.

 

[Nic]

Hi,
The format of the textbox which reads 'Nov2021' is set to mmm yyyy in the properties list.
The text box which i want to simply read numbers as '112021' is not bound to a table.
I want it to appear as numbers so that when a user types in numbers, then these can be compared and if they match, then the text box will go green.

Hope this makes some sort of sense!

 

[bob fitz]

Hi,
The format of the textbox which reads 'Nov2021' is set to mmm yyyy in the properties list.
The text box which i want to simply read numbers as '112021' is not bound to a table.
I want it to appear as numbers so that when a user types in numbers, then these can be compared and if they match, then the text box will go green.

Hope this makes some sort of sense!

Do you not get the desired effect if you remove the format setting from the text box

 

[theDBguy]

I want it to appear as numbers so that when a user types in numbers, then these can be compared and if they match, then the text box will go green.

So, just do the conversion in the method you're using to compare the two values. If you want the values to be numbers, maybe you could use the DatePart() function.

 

[Nic]

If i remove the format setting of mmm yyyy from the text box (me.bestbefore), it just desplays as 28/11/2021 15:06:26. my format setting changes this to Nov2021
The textbox gets this info from another piece code which reads: Me.BestBefore = DateAdd("m", [ShelfLife], [Now])
This basically takes todays date and adds the number of months shelflife, in this case 15months

 

[theDBguy]

If i remove the format setting of mmm yyyy from the text box (me.bestbefore), it just desplays as 28/11/2021 15:06:26. my format setting changes this to Nov2021
The textbox gets this info from another piece code which reads: Me.BestBefore = DateAdd("m", [ShelfLife], [Now])
This basically takes todays date and adds the number of months shelflife, in this case 15months

Hi. Just FYI, the formatting of the data has nothing to do with its value. You can try changing the Format property to mmyyyy and see if it displays what you want. However, that doesn't mean you can now compare it with a value that the user enters as numbers. You'll have to perform the conversion in your comparison logic. Can you show us how you perform that comparison? Thanks.

 

[Nic]

Hi; thanks for your patience! (no expert here)!
So, hopefully this may help explain what im doing: The user (using an onscreen key pad) types numbers into a text box then hits enter; the code then compares it to another textbox populated from a table of data, and if it matches the textbox goes green and moves onto next.
This one is comparing a barcode number:

Private Sub InputOuterBarcodeNo_AfterUpdate()

If Me.InputOuterBarcodeNo = Me.OuterBarcodeNo Then
Me.InputOuterBarcodeNo.BackColor = vbGreen
Me.Label168.Visible = True
Me.InputSKUDeclaredWeight.Visible = True
Me.InputSKUDeclaredWeight.SetFocus
Else
Me.InputOuterBarcodeNo.BackColor = vbRed
End If

End Sub

 

[theDBguy]

Hi; thanks for your patience! (no expert here)!
So, hopefully this may help explain what im doing: The user (using an onscreen key pad) types numbers into a text box then hits enter; the code then compares it to another textbox populated from a table of data, and if it matches the textbox goes green and moves onto next.
This one is comparing a barcode number:

Private Sub InputOuterBarcodeNo_AfterUpdate()

If Me.InputOuterBarcodeNo = Me.OuterBarcodeNo Then
Me.InputOuterBarcodeNo.BackColor = vbGreen
Me.Label168.Visible = True
Me.InputSKUDeclaredWeight.Visible = True
Me.InputSKUDeclaredWeight.SetFocus
Else
Me.InputOuterBarcodeNo.BackColor = vbRed
End If

End Sub

Hi. Thanks for the additional information. But, let me try explaining it this way; and hopefully, you'll get what I am trying to say.

If I compare a number against a number, I should get a good answer. For example:

If Number = Number Then Good

If I compare a date against a date, I should also get a good answer.

If Date = Date Then Good

However, what you seem to be asking is to compare a number against a date, which is not so good.

If Number = Date Then Not Good

What I am trying to say is maybe you should do something like this:

If Number = ConvertDateToNumber Then Good

Hope that makes sense...

 

[Nic]

Thanks for your help, and yes, i get what you mean.
My prob though is that the users will only have a number key pad to enter somthing, so can only enter their date as a number i.e. 112021;
Should perhaps i convert this number to a date, rather than the date to a number?
(or am i trying to achieve the impossible here) sorry if i sound a bit dumb! its been days of scratching my head and reading my access book for dummies!

 

[theDBguy]

Should perhaps i convert this number to a date, rather than the date to a number?

Either way. You perform the conversion in your code. So, since the user can only enter numbers, you can convert the number into a date, or convert your date into a number. All I'm pointing out was, you need to make sure you're comparing the same data. Did you look into the DatePart() function, like I said earlier? Just curious...

 

[Nic]

Just reading up on the DatePart() function; trying to get my head around it!
Any code suggestions welcome; although i kinda like the buzz of sussing it out myself!

 

[theDBguy]

Just reading up on the DatePart() function; trying to get my head around it!
Any code suggestions welcome; although i kinda like the buzz of sussing it out myself!

Just as a test, you could try this.

If Me.UserEnteredTextbox = Format(Me.TextboxName,"mmyyyy") Then

 

[Pat Hartman]

If i remove the format setting of mmm yyyy from the text box (me.bestbefore), it just desplays as 28/11/2021 15:06:26.

One of the problems is populating this field with a time. I'm pretty sure "best before" doesn't really care about the time so start by changing that field and remove all the times to leave just the dates. Then fix the routine that populates it initially to use Date() rather than Now().

If the users are going to enter only month and year, then you have to reformat BOTH the saved value and the value input in the text box to be yyyymm. You cannot validly compare two fields in the format mmyyyy except for equal. Is 112020 greater than 082021? It will be . That's why year needs to be first followed by date. 202011 will always compare less than 202108.

String compares are character by character from left to right.

PS, I don't know if your format is on the table field or on a control on a form but NEVER, EVER format table fields. All you are doing is hiding the real data. The format doesn't change anything.