Confusing Date Format Issue

[AUGuy]

I've got an issue with date formatting that I cannot resolve.
i have the following codes:

Dim CurrentDate As String
Dim rstDaily_Date As Object
Dim rstFile_Date As Object
Dim dbDatabase As Object
 
CurrentDate = Date
CurrentDate = Format(CurrentDate, "MMDDYYYY")
Set dbDatabase = CurrentDb
Set rstFile_Date = dbDatabase.OpenRecordset("File Date")
Set rstDaily_Date = dbDatabase.OpenRecordset("Date of Data")
 
    'Change Date of Data for Daily files to proper value
    rstDaily_Date.Edit
    rstDaily_Date("Date of Data").Value = rstFile_Date("LastRunDate")
    rstDaily_Date.Update
 
    'Change date to Current Date
    rstFile_Date.Edit
    rstFile_Date("LastRunDate").Value = CurrentDate
    rstFile_Date.Update

The tables/records its referring to are also formatted MMDDYYYY. however, when i attempt to run it, i get the error messagE:
"Run-time error '3421':
Data type conversion error.

Any thoughts on why this would be the case if everything appears to be in the same format?

Thanks in advance,
Guy

 

[pbaldy]

What line throws the error? I'd consolidate the 2 CurrentDate lines to 1, but that shouldn't throw an error.

I'm curious what the tables contain. Since you're opening the tables, you're editing whatever record the recordset finds first, unless they only contain one record.

 

[AUGuy]

rstFile_Date("LastRunDate").Value = CurrentDate

that's the culprit line according to debug.

Basically i use two dates to timestamp different things. The FILE DATE(last run date) which is the CurrentDate, and EFFECTIVE DATE. The effective date is always the last business day the process was run. So for instance, if it was run on last friday(the 30th), when i came in this morning and downloaded the new data the file date would be 07/30/2010 which would need to be copied to the effective date folder and then subsequently updated. What it does is copies the FILE DATE to the EFFECTIVE DATE and then updates the file date with the CurrentDate variable.

This is just a portion of the morning process module i've created, but its the only part that isnt working right now. If i change all the records to text format and the variables as well, it works fine. But i need it to be specific date format to work well with other tables and processes that i have, and this is where my problem is!

hope this helps clarify the issue.

edit: i still feel like this might be confusing so let me try to break it down again.

First thing this morning the dates read as follows
FileDate: 07/30/2010
Effective Date: 07/29/2010

I need the Effective date to now be 07/30/2010 so this process copies it over.
I now need the new File Date to be 08/02/2010 (the day the file was downloaded). the CurrentDate field holds thsi value and so File Date is set to it.

 

[pbaldy]

LastRunDate is a text data type, right? Just in case something's getting goofed up, try this line and delete the first one setting the variable to Date:

CurrentDate = Format(Date(), "MMDDYYYY")

If that doesn't work, when it dumps into debug mode, hover over the variable to see what it contains or type this in the Immediate window:

?CurrentDate

Personally I would use a date data type, since you are dealing with dates. The text field won't sort properly if you ever need it to. If the text field works for you, then I guess leave it as is.

 

[AUGuy]

When i make your suggested change I get an overflow error for this line:

CurrentDate = Format(CurrentDate, "MMDDYYYY")

when i hover over it it shows "08032010".

As far as sorting, that wont ever happen with this particular process. Each table is only one field and is used in some append queries. I realize it may not be the best way to do it, but its what i came up with

 

[ghudson]

When i make your suggested change I get an overflow error for this line:

CurrentDate = Format(CurrentDate, "MMDDYYYY")

when i hover over it it shows "08032010".

As far as sorting, that wont ever happen with this particular process. Each table is only one field and is used in some append queries. I realize it may not be the best way to do it, but its what i came up with

You are not using the code Paul gave you. Try...

CurrentDate = Format(Date(), "MMDDYYYY")

...to get the current system date.

 

[ghudson]

I would try this...

Dim sCurrentDay As String
Dim sCurrentDate As String
Dim dbDatabase As Object
Dim rstFile_Date As Object
Dim Answer As Integer
Dim rstDaily_Date As Object
Set dbDatabase = CurrentDb
Set rstFile_Date = dbDatabase.OpenRecordset("File Date")
Set rstDaily_Date = dbDatabase.OpenRecordset("Date of Data")
sCurrentDay = Format(Now(), "ddd")
sCurrentDate = Format(Now(), "MMDDYYYY")

What is the format of the dates in the "File Date" field? Are they the same as the format of the sCurrentDate?

If DMax("LastRunDate", "File Date") <> sCurrentDate Then

 

[AUGuy]

getting a type mismatch on

CurrentDate = Format(Now(), "MMDDYYYY")

hover and it shows 12:00:00 AM

 

[ghudson]

Now sure why. I just tested it and I got the following results in the debug window.

Dim [B]s[/B]CurrentDay As String
Dim [B]s[/B]CurrentDate As String
[B]s[/B]CurrentDay = Format(Now(), "ddd")
[B]s[/B]CurrentDate = Format(Now(), "MMDDYYYY")

Debug.Print [B]s[/B]CurrentDay
Debug.Print [B]s[/B]CurrentDate

Tue
08032010

Notice I added the s prefix to designate that these are strings.

 

[AUGuy]

You have taught me two important lessons:

1- Proper variable naming is conducive to smarter debugging.
2- I need to wear my glasses when writing code because i was dim'ing CurrentDate as Date and handling it like a string

Thanks for the help and patience, guys!

 

[ghudson]

Then my work here is done. Good luck! ;-)

 

[AUGuy]

You guys seriously are awesome! I'm able to be so much more beneficial and productive at work. Learning stuff on this site has actually inspired me to start classes to learn even more about VBA applications. Thanks!