Format Date query

[fluffyvampirekitten]

I want to format the date for DOB however it still did not show format that I want.

Current Format is : 18.11.2015
The format that i want is 18/11/2015.

Any ideas where went wrong ? kindly help me


Thanks in advance

SELECT UPDATE_SAP.ID, UPDATE_SAP.EXT_PAT_ID, UPDATE_SAP.PAT_NAME, UPDATE_SAP.GENDER, Format([DOB]," dd/mm/yyyy ") AS DATEOFBIRTH, UPDATE_SAP.CREATE_DATE, UPDATE_SAP.LAST_UPDATE_DATE, UPDATE_SAP.C_SOURCE, UPDATE_SAP.CAT_TYPE
FROM UPDATE_SAP
WHERE (((UPDATE_SAP.EXT_PAT_ID)>(" - ")));

[JoeM]

The FORMAT function will only work if your date field is of Data Type. If the format function is not working on it, i have a feeling it is Text/String data type.

If you want to convert it to a date, you can do that like this:

DATESERIAL(RIGHT([DOB],4),MID([DOB],4,2),LEFT([DOB],2))

That will return a valid date field.
You can then apply custom formatting on it to get it to display the date that you want.
Note that if you use the FORMAT function on this, it will convert the entry to Text again, so you won't be able to apply mathematical functions on sorting on it.

[fluffyvampirekitten]

The FORMAT function will only work if your date field is of Data Type. If the format function is not working on it, i have a feeling it is Text/String data type.

If you want to convert it to a date, you can do that like this:

DATESERIAL(RIGHT([DOB],4),MID([DOB],4,2),LEFT([DOB],2))

That will return a valid date field.
You can then apply custom formatting on it to get it to display the date that you want.
Note that if you use the FORMAT function on this, it will convert the entry to Text again, so you won't be able to apply mathematical functions on sorting on it.

Thansk for the help . It works
Does it mean that I can't use the count(*) function ?
Does it format will convert to Text again when I do "UNION function" ?

[JoeM]

The Aggregate Count function should work on any field format. It is more of an issue if you are trying to do some sort of date comparison, such as sorting by date or seeing if the date falls within a certain date range.

The UNION function itself should not change the format of any field. Just make sure that the records you are joining all have the same data types in their respective order. For example, if field 3 in Table 1 has a Date data type, make sure that field 3 in Table 2 also has a Date data type. The data types should all line up between the two tables/queries you are joining in your Union query.

[fluffyvampirekitten]

The Aggregate Count function should work on any field format. It is more of an issue if you are trying to do some sort of date comparison, such as sorting by date or seeing if the date falls within a certain date range.

The UNION function itself should not change the format of any field. Just make sure that the records you are joining all have the same data types in their respective order. For example, if field 3 in Table 1 has a Date data type, make sure that field 3 in Table 2 also has a Date data type. The data types should all line up between the two tables/queries you are joining in your Union query.

Ah i see. Thanks for the info.

I normalize my excel file make it all to "Short date" format in excel and access.
Thanks alot . Appreciated the help