Another CDate() question

[DaaAgent]

Hi All, Its been a while

I Keep getting an #Error result on the CDate function

1st step works fine
txtBookedDate: Trim(Mid([BookingDumpScreen],InStr([BookingDumpScreen],"P.")+4,8))
Answer as expected 07JAN15

2nd step converted the text to date
Tried BookedDate: CDate([txtBookedDate])
Answer #Error
Then tried BookedDate: CDate(Format([txtBookedDate],"dd/mm/yyyy"))
Answer #Error

I have tried so many combinations I have lost count. Even tried splitting the 07JAN15 date into a Day field then a Month and year field then build them all back together as dd/mm/yyyy see below

DateBookedDay: Left([txtBookedDate],2)
DateBookedMonth: Mid([txtBookedDate],3,3)
DateBookedYear: Mid([txtBookedDate],6,3)
DateBookedBuild: [DateBookedDay] & "/" & [DateBookedMonth] & "/" & [DateBookedYear]
BookedDate: CDate([DateBookedBuild])

This gives me as required 07/01/2015 in the query and when i select the BookedDate value the little calendar icon appears which I presume confirms it’s classed as a date field.
When I convert the select query over to an Append query and assign the BookedDate to the required table field and run it I get an Enter Parameter window asking for the DateBookedBuild value which i didn't get when it was a select query.

I feel I have over complicated a simple matter? Help… what I am I missing?

Thanks

 

[MarkK]

I get an Enter Parameter window

This occurs if the table doesn't contain the specified field name. Check the field names in the table again, carefully, then look at the SQL again. Keep in mind that an INSERT query might have two tables in it, the source AND the destination.

INSERT INTO tDestination ( Field1, Field2)
SELECT Field3, Field4 FROM tSource
WHERE SomeCondition

. . . so just because it worked in your SELECT query doesn't mean that field also exists in the destination table of your INSERT.
Hope that helps,

 

[DaaAgent]

Hi MarkK
Hmm Ok i will check in the Append SQL in the morning....Any ideas on the CDate part to simply converting the ddmmmyy text value into a dd/mm/yyyy date value?

 

[namliam]

You havent overcomplicated it... but running an append query isnt quite the same as running a select. they are not 1 and the same thing

Now that you have done it is small steps simply roll up your end result into one column like so:
BookedDate: CDate( Left([txtBookedDate],2)
& "/" & Mid([txtBookedDate],3,3)
& "/" & Mid([txtBookedDate],6,3)
)

and you should be golden

 

[DaaAgent]

Hi namliam & MarkK

I am now getting an Enter Parameter window for txtBookedDate when it’s a Append query. So Append queries only append from Table/Field to Table/Field. Can’t believe over the years I haven’t had a similar issue, anyway I was originally only using the select query to check all my Instr Mid Left etc was pulling out the correct values for the fields.

Think I will just scrap the append query’s and write direct to the tables through a form using VBA to split the Memo field into lines and build an array

Thanks again

 

[namliam]

Well if txtbookeddate is a control on a form you should reference it using something like:
forms("Yourformname").txtBookedDate, however that should be needed in a Select statement as well.

 

[MarkK]

Your month a string, not a number. Try . . .

CDate([DateBookedDay] & "-" & [DateBookedMonth] & "-" & [DateBookedYear])

but this doesn't explain your [Enter Parameter] thing . . .

 

[Cronk]

Going back to the original post.

cdate("07JAN15") will give an error because the string is not recognized as a date.
OTOH cdate("07 JAN 15") is.