Solved Problem in conversation of date

[lumiere]

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]

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]

Hi

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

 

[jdraw]

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]

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]

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]

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.

 

[lumiere]

Your suggestion to use yyyymmdd format was very helpful. Thank you sir.

 

[lumiere]

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]

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]

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]

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]

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]

Hi
I solved the problem by using year, month and day function and adding it to serial no.



Thanks for all your helpful suggestions.

 

[theDBguy]

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.