SQL Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Any SQL gurus that could advise why I cannot get this SQL to work?

strSQL4 = "SELECT [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= '" & tdyDte & "')
" & _
"AND (([2 Current Schedule].[Job Start Time])>= #07:00:00 AM# " & _
"AND ([2 Current Schedule].[Job Start Time])<= #23:59:59 PM#));"

tdyDte is a date varible in VBA.

Thanks,
GLT.
 
Even if it's a variable, you must enclose it in # because the end result of
the concatenations is a string. Print the string strSQL4 somewhere to make
sure that everything is OK.

If you want to use the variable tdyDte without #, you must use parameters
inside your query.

S. L.
 
Hi Sylvain,

Not sure if I got what you were saying right or not, but I modified it as
follows:

strSQL4 = "SELECT [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= '#" & tdyDte &
"#') " & _
"AND (([2 Current Schedule].[Job Start Time])>= #07:00:00 AM# " & _
"AND ([2 Current Schedule].[Job Start Time])<= #23:59:59 PM#));"

When I debug it, is shows as:

"SELECT [2 Current Schedule].* FROM [2 Current Schedule] WHERE ((([2 Current
Schedule].[Job Run Date])= '#6/04/2005 12:27:49 AM#') AND (([2 Current
Schedule].[Job Start Time])>= #07:00:00 AM# AND ([2 Current Schedule].[Job
Start Time])<= #23:59:59 PM#)"

Which produces the following error:

The error that I get is 'run time error '2342':
A Run SQL action requires an argument consisting of an SQL statement.

Thanks,
GLT




Sylvain Lafontaine said:
Even if it's a variable, you must enclose it in # because the end result of
the concatenations is a string. Print the string strSQL4 somewhere to make
sure that everything is OK.

If you want to use the variable tdyDte without #, you must use parameters
inside your query.

S. L.

GLT said:
Hi,

Any SQL gurus that could advise why I cannot get this SQL to work?

strSQL4 = "SELECT [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= '" & tdyDte & "')
" & _
"AND (([2 Current Schedule].[Job Start Time])>= #07:00:00 AM# " &
_
"AND ([2 Current Schedule].[Job Start Time])<= #23:59:59 PM#));"

tdyDte is a date varible in VBA.

Thanks,
GLT.
Click to expand...
Click to expand...
 
You have kept the single quotes ' around the first date: '#6/04/2005
12:27:49 AM#' ; you must remove them.

S. L.

GLT said:
Hi Sylvain,

Not sure if I got what you were saying right or not, but I modified it as
follows:

strSQL4 = "SELECT [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= '#" & tdyDte &
"#') " & _
"AND (([2 Current Schedule].[Job Start Time])>= #07:00:00 AM# " &
_
"AND ([2 Current Schedule].[Job Start Time])<= #23:59:59 PM#));"

When I debug it, is shows as:

"SELECT [2 Current Schedule].* FROM [2 Current Schedule] WHERE ((([2
Current
Schedule].[Job Run Date])= '#6/04/2005 12:27:49 AM#') AND (([2 Current
Schedule].[Job Start Time])>= #07:00:00 AM# AND ([2 Current Schedule].[Job
Start Time])<= #23:59:59 PM#)"

Which produces the following error:

The error that I get is 'run time error '2342':
A Run SQL action requires an argument consisting of an SQL statement.

Thanks,
GLT




Sylvain Lafontaine said:
Even if it's a variable, you must enclose it in # because the end result
of
the concatenations is a string. Print the string strSQL4 somewhere to
make
sure that everything is OK.

If you want to use the variable tdyDte without #, you must use parameters
inside your query.

S. L.

GLT said:
Hi,

Any SQL gurus that could advise why I cannot get this SQL to work?

strSQL4 = "SELECT [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= '" & tdyDte &
"')
" & _
"AND (([2 Current Schedule].[Job Start Time])>= #07:00:00 AM#
" &
_
"AND ([2 Current Schedule].[Job Start Time])<= #23:59:59
PM#));"

tdyDte is a date varible in VBA.

Thanks,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top