Left function giving my a invalid syntax error 1

[vba317]

I am using access 2002. I have a table with a field called BillMonth that has a date with the month in it in a text format. Ex. 201304 I am trying to write an update query for the next field called Yr. Both fields in the table are text. I am getting an invalid syntax error. In design view my query looks like this:

Field YrLeft[BillMonth],4)
Table RPT_Detail

Any help is appreciated

Tom

 

[PHV]

Field Yr:Left([BillMonth],4)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Sorry, that didnt work.
I have read it could be a reference issue. I checked and I don't have any missing errors.
This is my current refernce structure:

Visual Basic for applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
OLE automation
Microsoft Visual Basic for applications extensibility 5.3
Microsoft Office XP Web Components
Microsoft Excel 10.0 Object Library
Microsoft ADO Ext 2.8 for DDL and Security
Microsoft ActiveX Data Objectd 2.8 Library


Tom

 

[SkipVought]

I have a table with a field called BillMonth that has a date

Sorry, but what you have is not a "date" but a Year and Month number value.

You would be so far better off with a real date, from which Year or Month could be extracted very simply!

YYYYMM strings are vitrually useless as dates, IMNSHO!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Is it possible that any of the records might not have a value in the field? Does absolutely every record have a value matching the expected format?

Have you tried compiling your code?

What do you get if you open the debug window (press Ctrl+G) and enter:

? Left("201304",4)

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

How is this the design view for an UPDATE query?
Mine would be more like this:

[tt]Field: Yr
Table: RPT_detail
Update To: Left([BillMonth],4)[/tt]

Randy

 

[vba317]

Dhookup I tried your idea of typing in ?Left("201304",4)
I get 2013 returned to me which is what I want. So why won't it work in a query?

 

[dhookom]

vba317,
You didn't answer a couple of my questions:

Is it possible that any of the records might not have a value in the field? Does absolutely every record have a value matching the expected format?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I also ran a query for null values on the table and I had no records return to me.

Tom

 

[vba317]

Randy,
I reformatted my update query and I still get the invalid syntax error after the error the comma before the 4 is highlighted.

Tom

 

[PHV]

Depending on your locale settings you may have to use a semi-colon ( instead of a comma (,)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thank you PH but I tried the ; and I still get the same error.

Tom

 

[vba317]

DHookup I scanned through the records all 18500 of them and I did not see any data errors.

Tom

 

[randy700]

How about posting the code behind your query?


Randy

 

[vba317]

I have another idea. I have another table that has the date and year information in it. I wrote another query that used this other table and it works fine. I didnt know why my predessor created this table but it must be for this situation. I did want to thank everyone for ther help.
Tom

UPDATE dbo_dic_Period INNER JOIN RPT_Detail ON dbo_dic_Period.cache_id = RPT_Detail.BillMonth SET RPT_Detail.[Year] = [dbo_dic_Period].[yr];

 

[PHV]

So, the following didn't work ?
UPDATE RPT_Detail SET RPT_Detail.[Year] = Left(RPT_Detail.BillMonth, 4)




Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,
As usual you are great! Your code did work! I noticed that when I go back to design view I get:
Left(RPT_Detail.BillMonth~4).

So this is where I made my mistake. Thank you so much for your help! have a great day!

Tom