Syntax problem - probably with partdate?

[Guest]

I have the following code included in the afterupdate event of a control on a
form but I get the following error:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 2) And (([Issues].[ActivityID]) = 172) And
((DatePart('y',([Issues].[LastUpdate]))) = DatePart('y', 19/03/2006
11:25:29)))'

Can anybody explain what the problem is?

Thanks

 

[Allen Browne]

Firstly, if you are looking at the Year of the date, use "yyyy". "y" gives
the day of the year.

Secondly, a literal date/time needs to be delimited in #. It also needs to
be in m/d/y format (if you are looking at the SQL statement.)

Try:
((Issues.IssueNo = 2) And ((Issues.ActivityID = 172) And
(DatePart('yyyy',[Issues].[LastUpdate]) =
DatePart('yyyy', #03/19/2006 11:25:29#)))

 

[Douglas J. Steele]

Literal dates in Access must be delimited with # symbols. As well, in SQL
statements, you must use mm/dd/yyyy format, regardless of your Short Date
format*. Try

'(((Issues.IssueNo) = 2) And (([Issues].[ActivityID]) = 172) And
((DatePart('y',([Issues].[LastUpdate]))) = DatePart('y', #03/19/2006
11:25:29#)))'

Of course, I don't see any reason for using the DatePart function on a
literal date. What not just put

((DatePart('y',([Issues].[LastUpdate]))) = 78))

*Okay, this statement isn't 100% true. You can use any unambigous format as
well, such as dd mmm, yyyy or yyyy-mm-dd. In fact, in your specific example,
you can get away with 19/03/2006, since Access will realize that there isn't
a 19th month, and will interpret the date as dd/mm/yyyy. The point is, if
you put 11/03/2006, Access is going to interpret that as 3 Nov, not 11 Mar,
even though you've set your short date format to dd/mm/yyyy through Regional
Settings.

 

[Guest]

Hi,

Thanks for the quick response.

I should have explained that the date/time '19/03/2006 11:25:29' is taken
from a control on a form i.e. it is not a literal date that I have entered to
the code. Does this change your advice?

I'm looking to extract records created on the same day i.e. where dd/yyyy in
[Issues].[LastUpdate] = dd/yyyy in the form control. Is 'y' the right option?

Thanks again.

Firstly, if you are looking at the Year of the date, use "yyyy". "y" gives
the day of the year.

Secondly, a literal date/time needs to be delimited in #. It also needs to
be in m/d/y format (if you are looking at the SQL statement.)

Try:
((Issues.IssueNo = 2) And ((Issues.ActivityID = 172) And
(DatePart('yyyy',[Issues].[LastUpdate]) =
DatePart('yyyy', #03/19/2006 11:25:29#)))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the following code included in the afterupdate event of a control on
a
form but I get the following error:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 2) And (([Issues].[ActivityID]) = 172) And
((DatePart('y',([Issues].[LastUpdate]))) = DatePart('y', 19/03/2006
11:25:29)))'

Can anybody explain what the problem is?

Thanks

 

[Allen Browne]

So you want to match just the date part of the field against the value of
the text box?

Try:
DateValue(Issues.LastUpdate) = DateValue(Forms!Form1!Text1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the quick response.

I should have explained that the date/time '19/03/2006 11:25:29' is taken
from a control on a form i.e. it is not a literal date that I have entered
to
the code. Does this change your advice?

I'm looking to extract records created on the same day i.e. where dd/yyyy
in
[Issues].[LastUpdate] = dd/yyyy in the form control. Is 'y' the right
option?

Thanks again.

Firstly, if you are looking at the Year of the date, use "yyyy". "y"
gives
the day of the year.

Secondly, a literal date/time needs to be delimited in #. It also needs
to
be in m/d/y format (if you are looking at the SQL statement.)

Try:
((Issues.IssueNo = 2) And ((Issues.ActivityID = 172) And
(DatePart('yyyy',[Issues].[LastUpdate]) =
DatePart('yyyy', #03/19/2006 11:25:29#)))

I have the following code included in the afterupdate event of a control
on
a
form but I get the following error:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 2) And (([Issues].[ActivityID]) = 172) And
((DatePart('y',([Issues].[LastUpdate]))) = DatePart('y', 19/03/2006
11:25:29)))'

Can anybody explain what the problem is?

 

[Guest]

Hi,

I've changed the code as suggested but I am still getting the same error
(i.e. Run time error 3075 Syntax error (missing operator) in query expression)

The code is:

strQuerySQL2 = "SELECT * FROM Issues WHERE (((Issues.IssueNo) = " &
Me.IssueNo & ") And (([Issues].[ActivityID]) = " &
[Forms]![ActivityStatus]![ActivityID] & ") And
((DateValue([Issues].[LastUpdate]) = DateValue(" &
[Forms]![ActivityStatus]![CurrentDateTime] & "))) ORDER BY Issues.IssueID;"

The error message is:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 1) And (([Issues].[ActivityID]) = 35) And
((DateValue([Issues].[LastUpdate]) = DateValue(19/03/2006
14:42:53))) ORDER BY Issues.IssueID;'

If I build the same query using the design grid, then it returns 10 records
as expected.

Help please!

So you want to match just the date part of the field against the value of
the text box?

Try:
DateValue(Issues.LastUpdate) = DateValue(Forms!Form1!Text1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the quick response.

I should have explained that the date/time '19/03/2006 11:25:29' is taken
from a control on a form i.e. it is not a literal date that I have entered
to
the code. Does this change your advice?

I'm looking to extract records created on the same day i.e. where dd/yyyy
in
[Issues].[LastUpdate] = dd/yyyy in the form control. Is 'y' the right
option?

Thanks again.

Firstly, if you are looking at the Year of the date, use "yyyy". "y"
gives
the day of the year.

Secondly, a literal date/time needs to be delimited in #. It also needs
to
be in m/d/y format (if you are looking at the SQL statement.)

Try:
((Issues.IssueNo = 2) And ((Issues.ActivityID = 172) And
(DatePart('yyyy',[Issues].[LastUpdate]) =
DatePart('yyyy', #03/19/2006 11:25:29#)))

I have the following code included in the afterupdate event of a control
on
a
form but I get the following error:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 2) And (([Issues].[ActivityID]) = 172) And
((DatePart('y',([Issues].[LastUpdate]))) = DatePart('y', 19/03/2006
11:25:29)))'

Can anybody explain what the problem is?

 

[Douglas J. Steele]

As I said earlier, dates need to be delimited with # characters, and they
need to be in mm/dd/yyyy format:

strQuerySQL2 = "SELECT * FROM Issues WHERE (((Issues.IssueNo) = " &
Me.IssueNo & ") And (([Issues].[ActivityID]) = " &
[Forms]![ActivityStatus]![ActivityID] & ") And
((DateValue([Issues].[LastUpdate]) = DateValue(" &
Format([Forms]![ActivityStatus]![CurrentDateTime], "\#mm\/dd\/yyyy\# & ")))
ORDER BY Issues.IssueID;"

Remember, you may be getting it from a control on the form, but it's still a
literal date.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I've changed the code as suggested but I am still getting the same error
(i.e. Run time error 3075 Syntax error (missing operator) in query
expression)

The code is:

strQuerySQL2 = "SELECT * FROM Issues WHERE (((Issues.IssueNo) = " &
Me.IssueNo & ") And (([Issues].[ActivityID]) = " &
[Forms]![ActivityStatus]![ActivityID] & ") And
((DateValue([Issues].[LastUpdate]) = DateValue(" &
[Forms]![ActivityStatus]![CurrentDateTime] & "))) ORDER BY
Issues.IssueID;"

The error message is:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 1) And (([Issues].[ActivityID]) = 35) And
((DateValue([Issues].[LastUpdate]) = DateValue(19/03/2006
14:42:53))) ORDER BY Issues.IssueID;'

If I build the same query using the design grid, then it returns 10
records
as expected.

Help please!

So you want to match just the date part of the field against the value of
the text box?

Try:
DateValue(Issues.LastUpdate) = DateValue(Forms!Form1!Text1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the quick response.

I should have explained that the date/time '19/03/2006 11:25:29' is
taken
from a control on a form i.e. it is not a literal date that I have
entered
to
the code. Does this change your advice?

I'm looking to extract records created on the same day i.e. where
dd/yyyy
in
[Issues].[LastUpdate] = dd/yyyy in the form control. Is 'y' the right
option?

Thanks again.

:

Firstly, if you are looking at the Year of the date, use "yyyy". "y"
gives
the day of the year.

Secondly, a literal date/time needs to be delimited in #. It also
needs
to
be in m/d/y format (if you are looking at the SQL statement.)

Try:
((Issues.IssueNo = 2) And ((Issues.ActivityID = 172) And
(DatePart('yyyy',[Issues].[LastUpdate]) =
DatePart('yyyy', #03/19/2006 11:25:29#)))

I have the following code included in the afterupdate event of a
control
on
a
form but I get the following error:

Run time error 3075
Syntax error (missing operator) in query expression
'(((Issues.IssueNo) = 2) And (([Issues].[ActivityID]) = 172) And
((DatePart('y',([Issues].[LastUpdate]))) = DatePart('y', 19/03/2006
11:25:29)))'

Can anybody explain what the problem is?