Solved Problem in conversation of date (1 Viewer)

lumiere

New member
Local time
Today, 15:52
Joined
Nov 10, 2019
Messages
29
Hi,

I want to create a database where i can enter date, and serial number for customers. I want a third field which uses entered date as ddmmyyyy and joins it with serial number making it into a unique registration number.

Example - Date of entry - 1/30/2019
. Serial number - 22
. The registration number should be 3001201922. Is there a way to do it?

I have tried using =Format([DateField], "ddmmyyyy")&[SerialNo]

in default value of general tab of table properties but I get error of "The database engine does not recognize either the"DateField" in valuation expression, or the default value in table 'Table1'. "
I also tried using forms. I used the above formula in Property Sheet>Data>Control Source. It works, however the value doesn't show up in tables.



Can anyone guide me what I am doing wrong..
 

isladogs

MVP / VIP
Local time
Today, 22:52
Joined
Jan 14, 2017
Messages
18,186
If you really must save the value in a field, you could use an update query or a calculated field

However. there is no need to save the combined data separately as you are just duplicating data.
Instead I'd recommend that you have an autonumber primary key field to the table and just concatenate the values as required in a form / report control or a query.
Check your field names are correct: EntryDate or Date Of Entry? SerialNo or Serial Number?
 

mike60smart

Registered User.
Local time
Today, 22:52
Joined
Aug 6, 2017
Messages
1,899
Hi

Is the serial Number 22 going to be Unique to the One Customer?
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:52
Joined
Jan 23, 2006
Messages
15,364
lumiere,

I recommend you keep 1 fact in 1 field in data storage (tables). If you need to display some combination of fields, then concatenate and do so, but do not concatenate separate fields and store that combination.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
Hi. I was going to say a Calculated field might be a good candidate for this; but unfortunately, the Format() function is not available for use in a calculated field. In that case, your best bet is to use a calculated column in a query. You can use the same expression you have in a query. Good luck!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 28, 2001
Messages
27,001
I'm going to add my two cents into the consensus here and say, in the table keep the two fields separate. In a query, you can concatenate strings safely and almost never know the difference, because anything that requires a recordset source in Access can use a query in place of a table perfectly well.

HOWEVER, there are a couple of caveats here. You said the result would be a unique registration number. No issue with that concept - but if you were planning to use this number as a prime key in some other table, you have to be careful about it. Consistency will be very important in that case.

On practical terms, I might suggest that you use a slightly different format for your unique ID number. Use the "yyyymmdd" and "00" as the formats for the two parts, then concatenate the parts in that order. If you do, then that ID can be used as a proper key for sorting in order of customer ID entry. It will be fixed width and the higher the number, the more recently the customer was added. That could be useful.

However, there is another issue. You said "ID NUMBER" not ID string, so you are apparently contemplating leaving this result as something numeric. A LONG integer can hold numbers up to just over 2 billion, which is a 2 and 9 more digits. If you ever have more than 99 customers in a day then you have a potential for overflow with 8 digits for a date and 2 more digits for the day's unique number contribution. With the format starting with the year and with a 2-digit sequence number, you are good until some time in the year 2147. That should be adequate for now.

If this is a string, no issue at all. If it is to be a number, consider the potential ranges of what you do and remember that in ddmmyyyy formats, that first digit COULD be a 3 near the end of any month. At which point, even a 2-digit sequence number would not work correctly.
 

lumiere

New member
Local time
Today, 15:52
Joined
Nov 10, 2019
Messages
29
I tried using update query but have to close the form after data entry and reopen the form, run the update query, close the form again, open the table to reflect the changes in tables.
 

Attachments

  • Joining.accdb
    608 KB · Views: 103

lumiere

New member
Local time
Today, 15:52
Joined
Nov 10, 2019
Messages
29
Your suggestion to use yyyymmdd format was very helpful. Thank you sir.
 

lumiere

New member
Local time
Today, 15:52
Joined
Nov 10, 2019
Messages
29
Till now, I used manual register to enter customers details such as date of transaction, serial number and order details. I wanted to shift to computers for recording of transactions.
I wanted a column containing a unique registration number for every customer so that it can be used in future references. Registration number would be printed on receipt. Customers may come some days later with their receipts and registration number be used to track their info. A same customer could come at different times of the day and would be getting a different serial number. Also, different customers with same first or last number could come which would be assigned to different serial numbers.
I can’t use serial number as unique identifier as although serial number is unique to a day’s transaction, next day’s serial number register begins at 1.
 

isladogs

MVP / VIP
Local time
Today, 22:52
Joined
Jan 14, 2017
Messages
18,186
I haven't looked at your database but you could try adding Me.Recalc after running the update query.

However I still recommend that you don't save the calculated value. Just concatenate the other 2 fields as you need it
 

mike60smart

Registered User.
Local time
Today, 22:52
Joined
Aug 6, 2017
Messages
1,899
Hi

Your uploaded database contains one table named "table1"

You need more than 1 table to record your Customer Transactions.

Can you upload your actual database?
 

lumiere

New member
Local time
Today, 15:52
Joined
Nov 10, 2019
Messages
29
Hi

Your uploaded database contains one table named "table1"

You need more than 1 table to record your Customer Transactions.

Can you upload your actual database?




I haven't made database yet. Just begun and struggling with registration of customer part.
 

mike60smart

Registered User.
Local time
Today, 22:52
Joined
Aug 6, 2017
Messages
1,899
Hi

If you think of your process:-

A Customer on a specific day will Order one or more Items.

This will mean you need a minimum of 3 tables.

The Northwind Database will give you an idea of how to create the tables.
 

lumiere

New member
Local time
Today, 15:52
Joined
Nov 10, 2019
Messages
29
Hi
I solved the problem by using year, month and day function and adding it to serial no.



Thanks for all your helpful suggestions.
 

Attachments

  • IMG_20191210_091336.jpg
    IMG_20191210_091336.jpg
    96.7 KB · Views: 87

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
Hi
I solved the problem by using year, month and day function and adding it to serial no.



Thanks for all your helpful suggestions.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom