Checking format

[0nyx175]

Hi guys,

I've got a database with an 'address 1' field. Sometimes if it's just a number, excel formats the field as a date. Is there anything to check the field to identify any that are showing as dates as opposed to just normal fields. For example identify all customers who have some variation of dd-mm-yyyy in the address-1 field.

 

[pr2-eugin]

0nyx175, could you explain a little bit more? With some sample data? and what you exactly wish to do?

 

[0nyx175]

just need to check if a field fits the following format:

"dd-mm-yy"

If it is down as 19 smith road then thats fine but if it's as 01-Jan-12 then i need to know

 

[pr2-eugin]

Try IsDate function.. If it returns True then it is of Date format.. Else it is of Text format..

? IsDate("19 smith road")
False
? IsDate("01-Jan-12")
True

 

[0nyx175]

Some fields are going to be correct and some aren't - wouldnt this only pick up if the overall field in the table is a date as opposed to one particular entry

 

[pr2-eugin]

Seriously I am lost.. What does the "some fields are going to be correct some aren't" mean? I do not know what data your table holds.. So you have to give some information, like some sample data of what you mean by "some fields are going to be correct some aren't"..

 

[0nyx175]

Okay, here's the table:

Table1IDNameAddress 11Bob smith11 Smith street2John Jons12 Acorn grove3Hal Jordan14-Dec-20134Wade Wilson29-May-20135Nathan Summers112 Bradford street

I need to make sure the addresses aren't accidentally formatted as dates by excel and then imported into the database so i'd need a query which pulls through just 3 and 4 in the above table

 

[0nyx175]

Sorry, that wasnt formatted well:

Name, address 1
Bob smith, 11 Smith street
John Jons, 12 Acorn grove
Hal Jordan, 14-Dec-2013
Wade Wilson, 29-May-2013
Nathan Summers, 112 Bradford street

 

[pr2-eugin]

You could have used the method I suggested..

SELECT theFieldsYouWant FROM theTableName
WHERE IsDate(theAddressFieldName) = True;

 

[0nyx175]

You could have used the method I suggested..

SELECT theFieldsYouWant FROM theTableName
WHERE IsDate(theAddressFieldName) = True;

worked like a charm - thanks.

 

[pr2-eugin]

Glad you got it working.. Good Luck..