Dates in short format formatted within a string

[ponneri]

Hi.

I have a form in which I fill some fields (3 text boxes, 2 combo boxes). The last field is also a text field (255 chars) that needs to be populated based on the other fields.

It's like a string concatenated.
The fields are date_in, date_out, item_name, status. My code is as follows :

me.status = "Recd " + item_name+ " on "+ str(date_in)+" and sent out on "+str(date_out)

The string appears but the dates which are actually in short date format (ex: 15-Oct-17, 22-Oct-17) get changed to "Recd Tapes on 10/15/17 and sent out on 10/22/17".

I need the dates to be in " short format as they were entered in the string".

Can someone help please ?

 

[RuralGuy]

Try using the "&" as a concatenation character instead of the "+".

 

[missinglinq]

In addition, you may also need to do the Formatting in your concatenated string itself:

me.status = "Recd " & Me.item_name & " on " & Format(Me.date_in, "dd-mmm-yy") & " and sent out on " & Format(Me.date_out,"dd-mmm-yy")

Linq ;0)>

 

[Pat Hartman]

You also need to use Format() to change the dates to the string format you prefer to see.

Internally dates are stored as double precision numbers - NOT STRINGS. Formatting is for human consumption only. Access assumes dates should be formatted for human consumption based on your default Windows date format. If you don't want to change that to be dd-mmm-yyyy, then you have to use Format() in the cases where you need to insert dates into a string.

me.status = "Recd " & Me.item_name & " on " & Format(Me.date_in, "dd-mmm-yyyy") & " and sent out on " & Format(Me.date_out, "dd-mmm-yyyy")

I added the "Me." to the other variables. If they are not form controls, then just remove the "Me." prefix. Using "Me." gives you intellisense and reduces the work the interpreter needs to do to determine where the variable name is defined.

The reason RuralGuy and I suggested changing the + for the & is because the & is the standard concatenation operator for VBA. The plus is an arithmetic operator that does addition. As a side effect, it will concatenate two variables if one of them is a string. However, if either variable is Null, the + will return Null whereas the & will return a ZLS (Zero-Length-String). You can use this difference in behavior to your advantage when you want to concatenate multiple items surrounded by spaces but don't want the extra spaces when one of the variables is null. The + is most commonly employed for names and addresses.

Me.FullName = (Me.FirstName + " ") & (Me.MiddleName + " ") & Me.LastName
The expressions inside the parentheses will return null when the variable is null so you won't end up with extra spaces in the result

 

[ponneri]

Thanks for all the help. Will try and see.

Concern is the date part in the string.

 

[ponneri]

One more thing.

Is there no need to convent the date into a string like str(format(.... ??

 

[RuralGuy]

The output of the Format() function is always a String.

 

[ponneri]

Great !

Thanks a ton !!