isDate() function says invalid date is valid

[striker73]

I am trying to use the isDate() function to do some data verification. I am importing data from one table to another and am checking text fields to see if they are dates or not. In the new table, they need to be set as dates. I have a query that I am running that is supposed to flag the entries that need to be fixed manually. For the most part, it appears to work, but sometimes I will run into a date like "1/1/1023" and the isDate() function will say that is a correct date. Can anyone help me out? Is there a better function to use?

Thanks!

 

[striker73]

It also says the date 2/25/802 is valid.

 

[jbpelletier]

both date are date...

2/25/802 = 25 feb of 802
1/1/1023 = 1 jan of 1023

isdate = true from 1 jan 100 to 31 dec 9999

u should try with a format(....) if u whant a specific format to be interpreted ie. yyyy/mm/dd

jb

 

[TonyJollans]

Hi striker73,

Well, they are valid dates - and will convert to a date format. You will need to do a range check on your dates if you want to exclude them.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[striker73]

Okay, that makes some sense. I'm trying this in my query:

IIf(Format[QuestionableDate],'mm/dd/yyyy') > #1/1/1999#,"Valid","Older than 1999")

The [QuestionableDate] field is originally 3/8/2002, but I format it to 03/08/2002. In the query, I still get "Older than 1999".

What's the deal?

 

[striker73]

I wonder if format() turns it into a string field... Damn dirty apes!

DateValue(Format([MyDate],'mm/dd/yyyy')) works now.

Man, I hate VBA.

 

[scking]

It's not VBA, it's simply understanding the usage, the same as C, TSQL, C++ or whatever. An alternative is to set the table to validate the dates so the user couldn't enter and invalid date, or it could be done in the form used to add or edit the date field, or it could be done in code if importing data. I'm not sure how the data gets into your system so these recommendations may not be valid.

From Access Help:
------------------------
DateValue Function
Returns a Variant (Date).





---------------------
scking@arinc.com
---------------------

 

[striker73]

Yeah, I'm building a web-based database for a client using PHP and SQL, but I'm pulling fata from their Access file. It appears that the file is messed up because the table I am trying to pull data from doesn't even display in the Tables tab.

If you create a query and do a select all from the table, you can view the table information. It's really kind of messy. I'd like to do all the data clean up in PHP, but I can't import the table because SQL Enterprise Manager is freaking out about the alleged date field not being all dates. I can't figure out how to get it to import this field as a text field or nvarchar.

 

[SkipVought]

You might be able to manipulate the date cleanup in Excel.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RoyVidar]

If it isn't corruption, then the table is probably hidden. Tools | Options - View tab, check the "Hidden objects"

Doesn't help much with the other things, though, but you can study the data type, validation rules...

Roy-Vidar

 

[striker73]

That's exactly what was happening! Interesting, haven't seen that before.