comparing against date value.. 1

[Extras]

Am working with CFMX and an Access database...

I have an optional form field for date values. To prevent an error, I am entering a default value of "1/1/1111" into the database.

Now, when I allow the user to modify their enteries, naturally, they should not see "1/1/1111" in the date field, I would prefer if they saw "Please Select"

So, how do I write the CFIF statment that will compare the value from the database with the fixed value of "1/1/1111"??

I have tried the below:

<cfif TVSch_Date2 EQ CreateODBCDate(1/1/1111)>

and,

<cfif TVSch_Date2 EQ (1&&quot;/&quot;&1&&quot;/&quot;&1111)>

Both times I get the output as:

07/28/1110 instead of &quot;Please Select&quot;...

any suggestions would be deeply appreciated!

 

[CFDude]

Odd... must be something with access. I get the same thing.

I'd suggest that you don't put a date in, if it is optional then you can test for a NULL value instead -- you mentioned you are doing this to prevent an error -- can you elaborate? You should be able to work around this easily enough.

However, a quick fix would be to just check the year value and if it is prior to a particular year, then exclude it:

<cfif Year(TVSch_Date2) lt 1900>
Year comes before 1900, exlude it...
<cfelse>
valid
</cfif>

HTH,
Tim P.

 

[r937]

if you must use fake dates (and i recommend against it), then pull the substitute out of the database with access sql

select iif(thedate=#1111-1-1#
          ,'Please select'
          ,Format(thedate,'mmm d, yyyy')
                 as datephrase

rudy

 

[Extras]

Thanks CFDude! Checking against the year is the best option. BTW...any idea on why it is not good to use fake dates? All my dates or contemporary - and I could see no way of getting around leaving a date field blank in Access so a default fake date seemed to be the only option.

The &quot;error&quot; was in my syntax when I trying to compare the value of the date inserted with the specific value of 1/1/1111...comparing the year resolved that issue!

thanks again!

 

[CFDude]

Glad it helped!

I personally don't like sticking any data in a table that is not necessary. It may not be much, but it could eventually add up. A NULL value is easier to work around than trying to figure out what data is in there and come up with ways to work around it. It may work fine, but if a new release of Access decides to recognize the date differently or if you decide to migrate to a different database, the code may not work. Whereas a null value is pretty universal.

How are you inserting the data? If you are using CFINSERT -- then that may be why. I've never liked that tag and have seen problems with that in the past. Instead you can use a query like this:

<cfquery name=&quot;qry1&quot; datasource=&quot;bar&quot;>
Insert into myTable(textFieldName<cfif form.DateField is not &quot;&quot;>,dateFieldName</cfif>)
Values('#form.field#',<cfif form.DateField is not &quot;&quot;>#CreateOdbcDate(form.Date)#</cfif>

</cfquery>

Something like that -- the formatting of the date may be different in access.