Visual Basic :: Comparing Dates

About Comparing Two Dates....
Hi there. I got a problem of determining by using vba code when the new price is to be effective. What I want to do is sum up Quantity * price of those items with order date before 13/8/2004 and those with order date = or > 13/8/2004.

Code:
A B C D
PriceEffective Date Quantiy Order Date
20 13/8/2003 20 13/2/2003
40 13/8/2004 40 20/5/2003
20 25/9/2003
30 1/2/2004
25 16/4/2004
50 24/6/2004
35 2/7/2004
40 18/7/2004
20 15/8/2004
30 25/8/2004

Can any one here please advise?
Cheers.

Comparing Dates
how do you check for format dd/mm/yyyy or mm/dd/yyyy

i am from sydney australia and i am having this major problem wiht one of my program its insane

Comparing Dates
I'm trying to create a report which reads from many log files and extracts the date the log was closed. The latest date should then be output as a line on my report. But I'm having trouble comparing the dates. The code I currently use is -

If DateDiff("d", enddate, mydate) > 0 Then
enddate = mydate
End If

For some reason it's not printing the correct result. The last date in my logs is 06/25/2001 but its printing 06/10/2001.

Could someone please tell me whats wrong with the code or another way around it, thanks.

Comparing Dates
Hi,

Thanks for all your help but where am I going wrong?

On formload I put a date into a label box and add the string dates
to the listbox

Private Sub Form_Load()

lblCurrentDate.Caption = Date
cmdUpdate.Enabled = False
List1.AddItem "31 March, 2001"
List1.AddItem "7 April, 2001"
List1.AddItem "14 April, 2001"
End Sub

I want to compare the date in the label to the dates in the listbox
so that if a date has passed and error message is displayed.

Private Sub List1_change()

Select Case List1.ListIndex
Case 0
If CDate(List1.Text) <= Date Then
MsgBox "Error!Date Passed"
else
carry on with procedure
End If

I've declared all variables involved genereally.

TKS

Ben

Comparing Dates
hi.

I'm having some trouble getting the following boolean to return true.

' rsprod(15) is a date field in my recordset

if rsprod(15) < rsnew(15) then print "true"

This statement will work with text fields in my program (if I plug in, say, 0 instead of 15). but can not get it to compare dates.

Can anyone help me out?

Thanks,

David

Comparing Dates With SQL
I want to open a query with all records that have a date greater than a date thats in another field of another table. When I step through the code everything works but when it opens the query it doesn't follow the criteria I set. I have a feeling theres something wrong with the syntax in SQL statement. Do you need to use # instead of &? Any suggestions?

rstIH.Open "SELECT * FROM IssueHistory WHERE Timestamp >= " & rst![dteIssueImport] & "", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Comparing Dates In Sql
Hi all,

I'm trying to compare 2 dates using an SQL string I've placed in my VB project. The SQL string will be run using a MS Access database...It may be easier to understand if I just post my code:

*******************************************
Public recSQL As Recordset
Public SQL As String

If txtDate.Text <> "" Then
___SQL = "SELECT * FROM tblSales " & _
________"WHERE date>' " & txtDate.Text & " ';"
End If

Set recSQL = mdbQuoteAndSalesDB.OpenRecordset(SQL, dbOpenDynaset)

********************************************

txtDate.text is a textbox with a date format of:
Thursday, October 11, 2001
All the records saved into the field date are also of the same format.

When I use this code to open a form with the queries that match, it doesn't work. It just gives me all the entries in the database.

Can Access not compare dates the way VB can?

If this isn't clear, please let me know.

Thanks,

Comparing Dates
I have a hierarchy of sign off dates (for products sales) that I must evaluate. The hierarchy is:

Agent
Account Manager
Office Manager

The Account Managers sign off date can not be before the Agent's and the Office Manager's sign off can not be before the Account Managers. I have that working.

However, it often happens that the Account Manager will sign off, and the Office Manager will sign off. This is perfectly acceptable. In other words, the Agent does not necessarily HAVE to enter a sign off date (although they are told to - salesmen enjoy ignoring such things I've discovered. )

So, when I the Account Manager goes to enter his sign off date, I'm try to say if the textbox for the Agents sign off date is NOT empty, i.e., there is date there, then make sure whatever that date is is not before the Agents sign off. Here is what I have
VB Code:
If txtAgentSignOff.Text <> vbNullString And CDate(txtOffMgr.Text) < CDate(txtAgentSignOff.Text) Then
But, it is giving me a "Type Mismatch error"

Why?

Comparing Dates
hi guys. i was just wondering how you compare dates. what i wanted to do is to compare two dates and see which one is earlier. One o the dates is retrieved from the calendar control and the other date is in a text box. PLease help. thanks.

Comparing Dates
Hi All,

Could someone help with this please?

I have two text fields which are filled up with two sets of dates (text1(0).text = "12/12/2005" and text1(1).text = "01/01/2005") how can I do a test to see if text1(1) is before text1(0) date if so show a message?

Thanks

Loftty

Comparing Dates In VB
hi,
is there any way using which i cud compare dates in VB?
for examnple cud i find if 23 MARCH 2005 comes before 25 MARCH 2005 or 27 JUNE 2007

thanks and regards
vivek.s

Comparing Two Dates
DateStarted is datafield I retrieve from the sql server db....

I want to compare this date with todays date to calculate how many months are left before the contract ends:

This is the line I am using (after opening DateStarted in rs..)

Text4.Text = DateDiff ("mm", "DateStarted", Now

The error is Run time error 13 type mismatch

Comparing 2 Dates
hi i am not good @VB6 but have found lots of answers on here so i thought i'd give this a try again.
i have an app that i have developed. it is a frontend to a DB on SQL. i need to know how to compare a date in one of the fields to the system clock on the computer.
there has to be at least one entry per year on the table so i have to find away to see if the date is greater than a year since the last entry. i'm not even sure how to start.

Comparing Dates
Greetings,

Hope everyone is doing well.

I realize something must be wrong with my code, below, because, when toggling through the code, when I come to the If, it is acting like the DT value "10/10/03 1:39:47 PM" is less than the prevDT value of "'9/9/02 3:34:52 PM"

I beleive I did the right thing by conforming all the date and time combinations to Cdate; but apparently I need to do a different kind of compare than <=.

Here is the gist of my code. Please tell me why my "If" is unreliable, and what I need to do to fix it.

prevTime = "15:34:52"
prevDate = "'09/09/2002 "
prevdt = CDate(prevDate & " " & prevTime) '9/9/02 3:34:52 PM
DT = CDate(.Fields(DATEFIELDINDX) & " " & .Fields(TIMEFIELDINDX)) ' "10/10/03 1:39:47 PM"

If prevdt <= DT Then
-----
-----
-----
End If

Thank you,
Jim

Comparing Dates
I have two dates. I would like to compare the dates and see which date come first. The date that i would like to compare is in dd/mm/yy format. But the following code that i am trying to use to compare the date is in mm/dd/yy format. Is there anyway that i can compare the date using dd/mm/yy format ?

VB Code:
compare = DateDiff("d", Text2.Text, Text1.Text)

Comparing Dates - Need Some Help
I want to take the server date and compare it to two static dates. I want to then be able to manipulate the dates on the dtpicker control.

Here is the code that I have but it does not work... It doesnt go into the if condition. The problem i am trying to solve is on users pcs there is various date formats which affect the date. 12/01/01 on another machine can be 01/12/01... I need to take all dates as one format mm/dd/yy and compare it to the two dates I have specified. If the date is between that period then make the dtpicker1.mindate = 12/01/01

Here is my code
I have an asp page:

I have a text box taking the value of the date

<input id=text1 name=text1 value="<%=Date%>">

then i have :

<script Lanaguage = "vbscript">
sub Window_Onload

Check = text1.value

if Check >= #01/01/01# and Check <= #02/02/01# then
msgbox(Check)
end if

end sub

Comparing Two Dates
Can anyone think of a way to compare two dates that may be in a different format ? For example, I wan't to check that someone has the correct version of a file on their pc: I know that the date on the file should be 20/10/2000, when I use vb to return the date from the local file it returns it in the short date format on the pc, which may be different i.e. 10/20/2000. Although those dates are the same, they are represented differently, so how can I compare the two, or make them both the same format ?????

Comparing Dates
how to compare 2 dates
I want to chech whether the differene between 2 dates is within 6 months
or more
datediff("m",date1,date2) is giving rounded values which i dont want
for dates "30/10/2005" and "01/04/2006" it is giving 6
and even for "3/10/2005" and "01/04/2006" it is giving 6
my main aim is to find differene between 2 dates is within 6 months
or more

Comparing Dates
Hi guys!
Hope anyone has some nice suggestions to this problem. What i do is a grab a date from a loggfile and
save it in a string, lets call it LogDate. My system is set to Swedish, and the loggfile date is in US format,
i.e MM/DD/YY, so i have a little routine that changes the Swedish standard to US standard, and saves that
in a string, called NewDate. I then want to compare LogDate and NewDate, and get the number of days
between the two dates. Suggestions, anyone? =)

Cheers!
Maverick

Edited by - Maverick2004 on 11/30/2004 4:18:37 AM

Comparing Dates
Hello All,

I have a database that holds file information. One of the fields holds the date and time that a file was modified. I need to pull out a list of the most recent dates per file. So, a file might have five different dates and there might be one hundred different files each with their own list of different dates. I want to list the most recent date per file. Can SQL do this alone or do I need to write a bit of code to help it.

Cyphin78

Comparing Dates
I have column in database that stores date and time as 11/12/02 11:12:35 AM. I want to compare any date and time entered by user with this column. How do I do that.

Doing like this gives error:

Dim userStr As String
Dim sSQL As String
'txtInput is input taken from user.
userStr = txtInput
sSQL = "Select * from Profile where LoginDtTm " & userStr

what should be format of input string: will it be like : > #11/12/01 12:13:15 AM #

Please advice.

Thanks,
DKV

Comparing Dates
I am having a real hard time comparing dates and times to get something to work. I think the time part is right....it appears to be working...

I am trying to create a program that reminds the user about something. So they enter the date and the time to be reminded.

So I have the if statement as follows:

If remindDate <= todaysDate then
if remindTime <= timeNow then
remind the user
end if
end if

So lets say i entered two reminders today:
one for the the 9th of august for 3:30pm( it will remind me at 3:30pm) but if i put it in for the 12th of august at 3:30pm, it will remind me today at 3:30pm......i think i am missing something for comparing dates....

Comparing Dates
Ok, this should work:

If (FormatDateTime(Time, vbShortTime) >= FormatDateTime(Reminders(i).dtmTime, vbShortTime)) And (FormatDateTime(Reminders(i).dtmDate, vbShortDate) >= FormatDateTime(Date, vbShortDate)) Then

do whatever

end if

but its not! haha....anyway...the time check works, if it is equal to or greater then the time it works.....

but the date part only works if it equals the date...

any ideas?

Problems Comparing Dates In VBA
Greetings all!
As usual I come to yourselves with a plea of help as I am an analyst of very little brain

The code I am looking at is:

Code:
If Date < FormatDateTime(strCutoffDate, vbLongDate) Then
MsgBox "...."
Else
....
strCutOffDate is a string "24-Oct-2005". A date definately in the past
The strange thing is, whenever I run this the comparison

Code:
Date < FormatDateTime(strCutoffDate, vbLongDate)
keeps returning TRUE! I've checked and there seems to be no localisation issues (ie me using Brit format, Excel using American), so I cannot see why this would be happening.

Any ideas?
Thanks in advance I have a feeling this is another Excel "feature"

~Shiv

Comparing Dates Of 2 Files
hi guys,

I;m stuck on something maybe someone can help.
I have 2 files both with the same name but in different dirctories, and I need to compare their "last modified" dates.
does anyone know how to do that?

Storing And Comparing Dates
Hi have a big problem with dates in my database app, my app use a lot of dates to have an historial for every truck into a plant, the app works well, but when use the saved dates to find truck this not work well, and also when save a new date this is saves wrong, this mean dd/mm/yy is sometimes mm/dd/yy, I'm developing in Window98SE Spanish and the OS used in the plant is Windows2K professional English, in my system the app work very well, but when run on Win2K English all reports using Dates fails.

PLEASE NEED HELP WITH THIS BECAUSE I NEED TO DELIVER THIS APP SOON !!!!

Thanks !!!

Comparing Lots Of Dates
I need to compare 6 different date fields. Some may be null or they all can have dates. How do I compare six different dates and get the most recent? Please help! I'm using vb6 ado and sql server.

Problem Comparing Dates
hey guys, anyone knows whats wrong with this code ?

Code:
strsql2 = "SELECT Codigo, Cliente, Usuario, Linha, Total, DataEmissao FROM TBLDADOS WHERE DataEmissao >= #" & Datelimit & "#" And codusuario = " & codusuario"

rs2.CursorLocation = adUseClient
rs2.Open strsql2, conexao, adOpenForwardOnly, adLockReadOnly
i get a type mismatch error

Comparing Two Dates In A Form
In my project I have a textbox called date,there is another one in the flex grid also containing a date value in a column,when enter is pressed after giving a value in the first textbox there is comparision of both of these dates,and if the textbox date is larger,5 should be multiplyed to the value in the text box and shown on a other textbox in the same form.

Private Sub cmdEnter_Click()
Dim strfine As String
If txttodaysdate < rs.Fields(4) Then 'the value in the 1st textbox comparision
strfine = 0

Else

rs.Fields (4) - txttodaysdate = Value
Value * 5 = ans
strfine = ans

Loop 'the loop is there for the value in the column of flexgrid inanother row

end when EOF of flex grid

strfine=add all ans
txtfine.show all ans

End If
End Sub

This is a very basic algorithum for this prosedure,the thing is how to actualy code something like this? At least the first part in comparing two date values and if the textbox one is less ,putting zero in another text box in the form?

Comparing Dates Resolved
i have a table that contains dates and rates, i want to create a query which returns the rate corresponding to todays date or the closest date in the past

(i know how to say select where date = a certain date but how do i do the above???)

any ideas????

Comparing Dates In Access?
I am creating a SQL statement as a string within VBScript. I need to know if you can compare("<" or ">") dates within Access. I know you can do it with SQLServer.

Here is the code:

Dim dtDate
dtDate = date()

strSQL = "Select * from Rentals where AvailFrom<" & dtDate

This is not working as it is still returning dates that are > that todays. The AvailFrom field is a Date Type in Access.

Any suggestions woud be appreciated

Comparing Dates In MS Access
Hi,

I want to compare the current system date with another date in a table. If the current system date is less than the other date, I want to update a column to say "Yes".

I'm getting system date with Date() and my other date from a field.

I'm trying to run an update query to update a column to "yes" if the following criteria is met:

Date() < [value from Access field]

This doesn't work. Anyone know how??

Thanks!!

Query Comparing Dates
I'm sure this must be an FAQ, but I can't seem to find it...

I'm using VB6 to query an Access 2000 database. I want to pull all records from a particular table "TEST" whose field "DATE" is greater than today (basically, in the future). I thought that

"SELECT * from TEST where DATE > " & FormatDateTime(Now, vbShortDate)& " ORDER BY DATE ASC"

was working, but today when I check, there are no results (even though there should be at least 3).

Any suggestions?

Thanks,
Lindsay

Comparing File Dates
How do you do the above???

i am farily new to programming in vb so plz put it in laymans terms

many thnaks for your help

Comparing Dates Down To Month Level
If I have a date in the format of 06/01/2002, and I want count exactly (rounding) how many years it's been since that date, what would I do?

For example, 05/17/2002 was two years ago (with rounding), but as of tomorrow it will be three years ago. What function would I use to do this?

(All I want to know is how many years it's been, no days or months).

Comparing Dates With Visual Foxpro And Ado
i'm developing an app using vfp6.0 as the database and i'm using ado with odbc to access the vfp database.

my problem is how do i compare date fields in the database with literal dates or date variables..in my code..

for example..this ado open statement gves error sayin.."Operand required"

rsdata.open "Select Date from [tablename] where date=#12/12/2001#",cn,adopenstatic,adlockoptimistic

and this too..doesn't work with a date variable

rsdata.open "Select Date from [Tablename] where date =#" & Datevariable & "#",cn,adopenstatic,adlockoptimistic

pls..help..how can this be done..???

Problem Comparing Dates With Sql And Excel
Ok, I've been scratching my brain over this one. If anyone could help me out, that would be great.

Basically, I'm using ADO to select data out of an excel sheet and place it in a recordset. When I create the sql string to open the recordset, I need to compare a date out of my excel sheet with a user typed date in a text box. I've tried the following two sql statements:

SELECT * FROM [General$] WHERE Format > 7/12/2005

and

SELECT * FROM [General$] WHERE Format > '7/12/2005'

"Format" is the column in the excel sheet and the other date is pulled out of the text box. When I put single quotes around the second date I get a type mismatch and when I put no quotes it works but doesn't pull out the right data. It doesn't really exclude any data.

I tried to look up what format dates are from excel and this is all I found:

• date (ADO datatype 7, adDate, using Jet; 135, adDBTimestamp, using ODBC)

So, anyone have any idea how to create an sql string that will correctly compare those two dates?

Thanks

Help Please Having Trouble Comparing Dates In Access
Im writing reports in access that are printed from a VB program. Now on one of the reports they want me to take the last completed date which in a field in each record and compare it to today's date to get a "how long has it been since this task was completed last" field on the report. I was wanting to know if I could just code it into Access report or do I have to add another field in the database and code it into VB to generate the field every time they enter a task?

I was in the code of the detail of the access report that I want this done on and made a label box on the report and called it lblLast I then typed in lblLast. and got no usable values to replace the caption of the label with what I want to show up on the report.

Any help would be appreciated :)

- Richarde

Comparing Dates Involving A Database
For a project that I'm working on, I have to compare two date, one of which is in a database (crated with access) and the other is the current date. I need the two dates to compare, and then if the current date is greater than the date in the database, then two more feilds of the database will be filled in. But I can't quite figure out to write information to rows that alrady exist.

Trouble Comparing Dates From A Database And TxtBox
I am having trouble comparing dates, this is what I have so far with a run time error (Object required):

If Val(dataOrders.Recordset.Fields("OrderDate").Value) >= txtStartDate.Text And dataOrders.Recordset.fields("OrderDate").value <= txtEndDate.Text Then

Can anyone help?

Problem In Comparing 2 Dates In Adodc.recordsource
I have to run a query
adodc1.recordsource = "select * from RawMat_Stock where S_Date = " & dt & " "
adodc1.refresh

S_Date is a field and dt holds system's current date .
There is a record in the table RawMat_Stock which has the S_Date = dt . But the recordset does not fetch that record . recordset.recordcount = 0 returns.

I want that if record exist than ignore otherwise add the record for that date.

So pls help me to run it.

Edited by - dilip347 on 1/22/2004 11:25:35 PM

Comparing Dates Before Transferring Data From Access DB To Excel (ADO)
I'm guess I need to transfer the info into a buffer (recordset?), do the comparison, then only extract the relevant data to Excel? Is there a simpler way of doing this? And does anyone have links to examples?

Must the Format be identical (eg. Long Date vs Long Date, or can it be like Long Date vs Short Date) when it comes to comparing dates?

Furthermore, when i use the preset MM/DD/YYYY, whenever i enter a date like '10/12/2006' (12 Oct), it automatically reverts to '12/10/2006'... and if i enter 12/15/2006' (15 Dec), it doesn't seem to recognise the date at all! (btw, i'm using British settings for win xp)

Thanks in advance... need all the help i can get.

Comparing Dates To Bring About A Specific Visual Detail
ok... using the some of the data from the database in my other inquiries (EOS), I want, after selecting the data from the EOS field in the database, to compare it to the date in a difference text box (txtDate.text). EOS is End Of Stay, and I want to make the comparison in order that the EOS, if it predates the date in txtDate, to return red text in the txtEos text box. Here is what I did, but it's not working in ALL cases (if either the 2 digit month value, OR the 2 digit date value OR the 4 digit year value are smaller, it returns the red text)For example... if the date in txtDate is 12/30/2004, and the date in txtEos is 01/06/2005, it returns text in the txtEos text box in red, even though the date in txtDate predates the date in txtEos.

The Code:

Private Sub txtEos_Change()

    If Len(txtEos.Text) < 2 Then ' If there is less than 2 number in the TextBox
        txtEos.Text = txtEos.Text ' Leave the Text the same
    ElseIf Len(txtEos.Text) = 2 Then ' If there is 2 numbers in the TextBox
        txtEos.Text = txtEos.Text & "/" ' Add a / after the 2 numbers
        txtEos.SelStart = Len(txtEos.Text) ' Keep the cursor at the end of the Text in the TextBox
    ElseIf Len(txtEos.Text) > 2 And Len(txtEos.Text) < 5 Then ' If the user has entered more than 2 numbers and less than 4
        txtEos.Text = txtEos.Text ' Leave the Text the same
    ElseIf Len(txtEos.Text) = 5 Then ' If the use has entered 4 numbers
        txtEos.Text = txtEos.Text & "/" ' Add a / after the second set ofnumbers
        txtEos.SelStart = Len(txtEos.Text) ' Keep the cursor at the end of the Text in the TextBox
    ElseIf Len(txtEos.Text) > 5 Then ' This is kind of redundant, but I put it here anyway. It just keeps the Text in the TextBox the same if the user has entered more than 4 numbers.
        txtEos.Text = txtEos.Text
    End If


    If txtEos.Text < txtDate.Text Then
        txtEos.ForeColor = &HFF&
    Else: txtEos.ForeColor = &H80000008

    End If

End Sub

Thank you very much for your help

Happieman

Edited by - Happieman on 5/21/2005 8:29:17 PM

Comparing Dates?</title> <script Type="text/javascript"> <!-- Function PrintTags() { Var Curr=document.getElementById('vB_Editor_QR_textarea').value; Newval=curr.replace(/[highlight]/gi,'[HIGHLIGHT=vb]'
alright. here is my question. I have two boxes with dates. One that has a beginning date, and one that has an ending date. I have a .dat file that contains a list of dates with info. The .dat file looks like this:

#2001-04-11#,"D14","701",.91
#2001-04-11#,"C87","712",2.05
#2001-04-11#,"L12","007",.94
#2001-04-11#,"L12","503",1.22
#2001-04-11#,"L12","102",.32
#2001-04-11#,"L12","711",1.51
#2001-04-11#,"L12","466",1.13
#2001-04-11#,"M32","200",3.73
#2001-04-11#,"M32","712",2.21
#2001-04-11#,"M32","096",1.44
#2001-04-11#,"C87","466",1.16
#2001-04-11#,"C87","201",2.93
#2001-04-11#,"C87","100",.27
#2001-04-11#,"C87","428",2.1
#2001-04-11#,"C87","101",.33
#2002-05-05#,"L12","007",.98
#2002-05-05#,"L12","007",.98
#2002-05-05#,"L12","123",1.23

The .dat file as been inported into my program, and the names of the data have the variables TransDate(K), TransEmpNum(K), TransUPC(K), TransPrice(K) respectivly. How do I sort the data to only pull the information in between the two dates inputed by the user? For example, if the user inputed 2/1/80 as from date and 2/4/02 as to date, how do I have the program pull information in between those 2 dates? DateDiff() ?? What I am planning on doing is then add similar products together, known by product code.. count the amount of units sold and the total value. But if you could help me with the dates, that would be great.

Thanks~

I Want To Read Files With The Dates From Oldest To Recent (in A Directory). The Dates Are Got From E
I want to read files with the dates from oldest to recent (in a directory). the dates are got from each specific file, that is, i need to read each file to find which date is the oldest to read first (compare or sort). please give me a bit detailed code. thanx

Change Dates Formated As Text To Dates In Excel From Vb6
I have a spreadsheet with mixed dates and dates expressed in text. From VB6 I want to programtically convert all text dates to dates. I know it shoudl be simple ... Im just not getting it ...

any help appreciated!

Added [RESOLVED] to thread title and green "resolved" checkmark - Hack

How Can I Calculate The No Of Days Between 2 Dates, Including The Selected Dates ?
Hi all

How can I calculate the no of days between 2 dates, including the selected dates ?

for eg : between 1st Jan & 31 Jan there is 31 days

but when I calculate like this

DTPicker1.Value = 01/01/2003
DTPicker2.Value = 31/01/2003
totaldays = DTPicker2.Value - DTPicker1.Value

The result I get is 30, but in fact it should be 31

Thanks

Peters A P

Compare Dates An Find If Dates Are A Week Apart
Hi all,
i would like to compare 2 dates an do an action if they are a week apart. I tried quite a few things to no prevail.
dim date 1 as date
dim date 2 as date
if date1 ARE A WEEK APART date2 then
do some thing
else
do other thing

early reply would be appreciated.
thanks

Kunal

Coercing Blank Dates To Look Like Dates In Excel
I have an Access table with some date/time columns. Some of these columns have blank dates. When I export this table to Excel, since the first 10 or so records for this column has blank dates, Access/Excel thinks it's a text field and so when some dates actually appear, they are prefixed with a single quote and behave like text. Then when we import this data back into another Access database, all hell breaks loose.

The original query had Code:Non-Accrual Date AsDate: IIf([Non-Accrual Date]="00/00/0000" Or [Non-Accrual Date] Is Null,Null,DateValue([Non-Accrual Date]))

I changed it to Code:Non-Accrual Date AsDate: IIf([Non-Accrual Date]="00/00/0000" Or [Non-Accrual Date] Is Null,"",DateValue([Non-Accrual Date]))

Neither way coerces the blank/null values. Any other ideas?

Postscript
The way to fix this manually in Excel is to remove all formats, the "shake the cell" by doing F2, Enter.

Code:'02/07/2002

Solution
I couldn't coerce the Excel cells, but when I re-imported the data into another database I used Code:Expr6: IIf([Non-Accrual Date AsDate] Is Null,Null,CDate([Non-Accrual Date AsDate]))

Edited by - Geof on 4/5/2006 7:07:08 AM