Solved DatePart not working in select query like (1 Viewer)

[why]

Any idea why this won't work?

SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS Expr2
FROM Closednumbers
WHERE (((Left([ClosedNumbers]![ClosedNumbers],2)) Like "Str(Right(DatePart("yyyy",Now()),2))"));

It says the expression you entered contains invalid syntax. You may have entered an operand without an operator. It then focuses on yyyy

If I run it like this it works

Str(Right(DatePart("yyyy",Now()),2))

and this also works

SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS Expr2
FROM Closednumbers
WHERE (((Left([ClosedNumbers]![ClosedNumbers],2)) Like "20"));

Any help would be great my brain hurts.

 

[Isaac]

Is it something more like this? I haven't done much of wildcards combined with expressions inside the wc, but:

WHERE Left([ClosedNumbers]![ClosedNumbers],2) Like '*Str(Right(DatePart("yyyy",Now()),2))*'

 

[theDBguy]

Try:

...=Str(Right(DatePart("yyyy",Now()),2))));

Don't need to use Like if you're not going to use wildcards.

 

[why]

Is it something more like this? I haven't done much of wildcards combined with expressions inside the wc, but:

WHERE Left([ClosedNumbers]![ClosedNumbers],2) Like '*Str(Right(DatePart("yyyy",Now()),2))*'

This runs but it returns no values.

Is it something more like this? I haven't done much of wildcards combined with expressions inside the wc, but:

WHERE Left([ClosedNumbers]![ClosedNumbers],2) Like '*Str(Right(DatePart("yyyy",Now()),2))*'

On this one, it runs but I get no values returned.

 

[theDBguy]

This runs but it returns no values.

On this one, it runs but I get no values returned.

Does mine run? What do you get?

 

[Isaac]

Hmm. Not sure to what extent you can combine expressions inside Like '*'

Another option is create a vba function that returns a literal string.

Edit -i see dbGuy's post, best one

 

[why]

Try:

...=Str(Right(DatePart("yyyy",Now()),2))));

Don't need to use Like if you're not going to use wildcards.

This runs but it also returns 0 values.

SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS Expr2
FROM Closednumbers
WHERE (Left([ClosedNumbers]![ClosedNumbers],2)) =Str(Right(DatePart("yyyy",Now()),2));

 

[theDBguy]

This runs but it also returns 0 values.

SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS Expr2
FROM Closednumbers
WHERE (Left([ClosedNumbers]![ClosedNumbers],2)) =Str(Right(DatePart("yyyy",Now()),2));

One more try then. What do you get with this?

...=Format(Date(),"yy")

Or this one?

...="'" & Format(Date(),"yy") & "'"

 

[why]

One more try then. What do you get with this?

...=Format(Date(),"yy")

Or this one?

...="'" & Format(Date(),"yy") & "'"

One more try then. What do you get with this?

...=Format(Date(),"yy")

Or this one?

...="'" & Format(Date(),"yy") & "'"

Thanks the first one worked here is the code

SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS Expr2
FROM Closednumbers
WHERE (Left([ClosedNumbers]![ClosedNumbers],2))=Format(Date(),"yy");

 

[theDBguy]

Thanks the first one worked here is the code

SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS Expr2
FROM Closednumbers
WHERE (Left([ClosedNumbers]![ClosedNumbers],2))=Format(Date(),"yy");

Okay, glad to hear you got it to work. Good luck with your project.