Syntax Error, Missing operator in SQL string

  • Thread starter Thread starter gr
  • Start date Start date
G

gr

Hi,
I have this code:

...
....
Dim qd as QueryDef
set qd = db.QueryDefs("namequery")
....
....

qd.SQL = strNewSQL 'ERROR!!!
....
....
'end code

WHERE:
strNewSQL = "SELECT [tblMain].[ListID], [tblMain].
[OfferNo], [tblMain].[Von], [tblMain].[Bis], [OfferNo]" _
& " <" _
& "Format([Von], dd\.mm)" _
& " - " _
& "Format([Bis], dd\.mm)" _
& ">" _
& " AS OfferLabel, " _
& "[tblMain].[ProjectName],
[tblMain].[Keyword], [tblMain].[Status]" _
& "FROM tblMain" _
& " WHERE tblMain.Von Between
DateAdd(m, -6, Now()) And Now() " _
& " ORDER BY tblMain.Von;"

SOMETHING IS WRONG IN THE strNewSQL STRING, CAN ANYONE
HELP?
I THINK THE PROBLEM IS IN Format([Von], dd.mm) but I have
tried Format([Von], "dd.mm") and Format([Von], dd\.mm)
and Format([tblMain].[Von], dd.mm) , etc, etc..

any ideas?
 
Hi,


...
& " - " _
& "Format([Bis], ""dd\.mm"")" _
& ">" _
...

where "" is two double quotes.


Note that you are NOT obliged to use a querydef to use a SQL statement. The
string of your SQL statement can be used for rowsource and for recordsource
properties, directly. There are some exceptions, such as Dxxx functions that
requires a saved query, but most of the interface do not necessary need that
we start with a querydef (or a named query).


Hoping it may help,
Vanderghast, Access MVP
 
Great! thank you =)
-----Original Message-----
Hi,


...
& " - " _
& "Format ([Bis], ""dd\.mm"")" _
& ">" _
...

where "" is two double quotes.


Note that you are NOT obliged to use a querydef to use a SQL statement. The
string of your SQL statement can be used for rowsource and for recordsource
properties, directly. There are some exceptions, such as Dxxx functions that
requires a saved query, but most of the interface do not necessary need that
we start with a querydef (or a named query).


Hoping it may help,
Vanderghast, Access MVP


Hi,
I have this code:

..
...
Dim qd as QueryDef
set qd = db.QueryDefs("namequery")
...
...

qd.SQL = strNewSQL 'ERROR!!!
...
...
'end code

WHERE:
strNewSQL = "SELECT [tblMain].[ListID], [tblMain].
[OfferNo], [tblMain].[Von], [tblMain].[Bis], [OfferNo]" _
& " <" _
& "Format([Von], dd\.mm)" _
& " - " _
& "Format([Bis], dd\.mm)" _
& ">" _
& " AS OfferLabel, " _
& "[tblMain].[ProjectName],
[tblMain].[Keyword], [tblMain].[Status]" _
& "FROM tblMain" _
& " WHERE tblMain.Von Between
DateAdd(m, -6, Now()) And Now() " _
& " ORDER BY tblMain.Von;"

SOMETHING IS WRONG IN THE strNewSQL STRING, CAN ANYONE
HELP?
I THINK THE PROBLEM IS IN Format([Von], dd.mm) but I have
tried Format([Von], "dd.mm") and Format([Von], dd\.mm)
and Format([tblMain].[Von], dd.mm) , etc, etc..

any ideas?
Click to expand...


.
Click to expand...
 
Back
Top