How do I concatenate 2 fields from table via query (and import result to table)?

[Trace]

Hello and thanks to the volunteers helping out here!

I want to generate an issue number using the date and time, with format yyyymmdd-hhnn.



In my table "Activity Description" I have three fields:

[IssueNumber] to be populated once concatenation works,
[DateInitiated] short text data type, format yyyy-mm-dd with default value =(Now()), showing a date picker for dates
[TimeInitiated] short text data type, short time format, default value =Time$()

What I want to happen is that, when a form is populated and then saved, the system date and time will be recorded in the fields above and also concatenate to make an IssueNumber, which will then also be stored in the table.

1) How do I make this work? Currently I am getting the following in my query:


2) Is this the best way to do what I want?

[Welshgasman]

Do not do it, period.
Now() already has the time included, plus you get the issue number just by formatting that field as you want.
Do not store data that can be calculated, as yours can here.
If you just want date then use Date()

[Trace]

OK, I won't use a query.
Default value =Now() gives me an Issue Number with format m/dd/yyyy h:mm:ss PM
What I'd like is format yyyymmdd-HHMM where HH is two digit hour two digit minute (24hr time)
I input yyyy-mm-dd-HH, but the NOW format doesn't seem to accept format changes.

[Welshgasman]

Now() and Date() are just numbers.
You format them anyway you want them.
You would still see your issue number in any query, just that it would be your field formatted as you want.

Edit:

? format(Now(),"yyyymmdd-HHMM")
20220921-2109

[Trace]

Thank you for your help and I apologize for any misunderstandings.
Here is what my table displays for Issue Number:


And here is the design view where the Issue Number field format is yyyy-mm-dd-hhnn but the Default Value is set to =Now(). They don't seem to be playing well together.


I had read somewhere that if my system time is set to 12 hr, I could change it to 24hr and then that would make some thing like =Now() display like what I want, BUT....
..would that mean that any users of the DB would ALSO have to use 24hr time on their machines?

[Welshgasman]

I do not get the AM/PM
However I can only assume as Format changes the data to a string type, it just puts the data in as system format.
Then it cannot be formatted as yyyy-mm-dd-hh-nn (nn is minutes BTW), so stays as it is.

As I stated, there is no need for Issue Number as you can calculate and format from whatever field the Now() is stored in?

If you insist on keeping that IssuesNumber as a field, then it should be a Date field, not text, and then formatted as you want, preferably in the form, but in the table if you have to.

[Trace]

Thanks for your help. I'll study your responses some more and keep trying your suggestions. I appreciate the advice!!!

[June7]

Format(DateInitiated + Nz(TimeInitiated,0), "yyyy-mm-dd-hhnn")

Is this a multiple simultaneous users split database? What if multiple users generate the same IssueNumber?

[Welshgasman]

@June7
Is TimeInitiated even needed?
Could have just one field DateTimeInitiated? Easy enough to get Date and Time from that one field?

[June7]

Yep, no idea why OP has separate fields.