What's wrong with my expression/query??

[tanyalee123]

Hi all!


So this is what I'm trying to accomplish. I have a spreadsheet that I imported into my DB (termdate) because I need to update the TERMDATE of an existing table (BH_Visit). Problem is that the spreadsheet I imported has a different format for the date. The spreadsheet presents the date in YYYYMMDD and I need MM/DD/YYYY. So I created an update query that looks like this. When I try to run it, Access tell me that my expression isn't valid. I've used this expression many times before because we have many dates that do not match our database. Can anyone see what I'm doing wrong, and do I have my query set up correctly? Thank you in advance for any assistance!!
(Field2 is the date)

[Xarkath]

Hello,

My guess is that it does not like the the Left and Right joined in such a way. Instead try "mid()". Mid([termdate].[Field2],5,2). That should result in the mm.

So TermDate: Mid([termdate].[Field2],5,2) & "/" & Right(([termdate].[Field2],2) & "/" & Left([termdate].[Field2],4) should get the results you are looking for. However, I have not tested this.

[ssanfu]

Edit: Oops. I missed the table name "TermDate". A table name "TermDate" and a field named "TermDate".... confusing


"Field:" means what field are you trying to update?
"Update To" is what you want to the above field to?

So you should have;

"Field:" TermDate
"Table:" BH_Visit
"Update To:" Left(Right([Field2],4),2) & "/" & Right([Field2],2) & "/" & Left([Field2],4)
"Criteria:" Is Null

But even that formula is weird. If "Field2" is a text field and "TermDate" is a Date/Time field, I would use:

DateSerial(Left([Field2],4), Mid(Field2,5,2), Right(Field2,2))

If "TermDate" is a text field, I would use:

Mid(Field2,5,2) & "/" & Right(Field2,2) & "/" & Left([Field2],4)

[tanyalee123]

Thank you so much for your help; I am trying this suggestion but it's saying it missing a bracket or parenthesis or vertical bar...I can't see what might be missing; any idea?

[ssanfu]

I missed an ampersand when I typed in your expression. Should be

"Field:" TermDate
"Table:" BH_Visit
"Update To:" Left(Right([Field2],4),2) & "/" & Right([Field2],2) & "/" & Left([Field2],4)
"Criteria:" Is Null

[tanyalee123]

Thank you everyone, those expressions are working, except when I run it, I am getting the message that there is a conversion failure, so it's not updating the field. I re-imported the spreadsheet and chose "date/time" for the field format. The field format for TermDate is also date/time, so why would there be a conversion failure? Does anyone have any ideas?

[ssanfu]

Can you post your dB?

[tanyalee123]

I wish I could, but it's patient data.

[ssanfu]

Doesn't have to be real data. Just a few records to be able to test.

The code works for me, but I created the two tables & test data.
Would need to see how the spreadsheet data is imported into Access.

[June7]

Make copy and remove/replace sensitive info (LastName, address, phone, email, ssn)

[tanyalee123]

OK I deleted pretty much everything and kept 4 fake people....but what the heck, am I blind, I can't see the attach option, just insert image, link, etc...

[June7]

Click Go Advanced below the Quick Reply editor. The Attachment Manager is below the Advanced Post Editor window.

[ssanfu]

Be sure and read the first line in June's signature (bottom of her posts).