VBA Varible in SQL Query

[Guest]

Hi I have the following code:

dteDte = Date()

strSQL4 = "DELETE [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= " & dteDte & " ) "
& _
"AND (([2 Current Schedule].[Job Start Time])>=#07:00:00# And ([2
Current Schedule].[Job Start Time])<=#23:59:59#));"

I have tried every different possibility I can think of with quotes around
the varible dteDte and nothing seems to work. The odd thing is I can use the
same varible in a SELECT statement (with single quotes around the double
quotes) and it works fine.

 

[Van T. Dinh]

Explicit date value must be in "mm/dd/yyyy" format and enclosed in hashes
(#). Try:

strSQL4 = "DELETE 2CS.* " & _
" FROM [2 Current Schedule] AS 2CS " & _
" WHERE ( 2CS.[Job Run Date]= " & Format(dteDte,"\#mm/dd/yyyy\#")
& " ) " & _
" AND ( 2CS.[Job Start Time] >= #07:00:00# ) " & _
" AND ( 2CS.[Job Start Time] <= #23:59:59# )"

 

[Marshall Barton]

dteDte = Date()

strSQL4 = "DELETE [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= " & dteDte & " ) "
& _
"AND (([2 Current Schedule].[Job Start Time])>=#07:00:00# And ([2
Current Schedule].[Job Start Time])<=#23:59:59#));"

I have tried every different possibility I can think of with quotes around
the varible dteDte and nothing seems to work. The odd thing is I can use the
same varible in a SELECT statement (with single quotes around the double
quotes) and it works fine.

Dates in Access are delimited by # signs.\

.. . .Date])= #" & dteDte & "# ) " . . .

which is all you need when entering USA style dates (m/d/y)
on a USA configured computer.

To be totally safe when you're not sure how the computer
might be configured for d/m/y kind of dates, use this:

.. . .Date])= " & FORMAT(dteDte, "\#m\/d\/yyyy\#) & " ) " . .
..

 

[Guest]

Hi Van,

Thanks for your response, I tried what you suggested and the following error
appeared:

Run-Time error '3075':

Syntax error (missing operator) in query expression '( 2CS.[Job Run Date]=
#04/16/2005# ) AND ( 2CS.[Job Start Time]>=#07:00:00# ) AND ( 2CS.[Job Start
Time] <= #23:59:59# )'.

I Debug'd it and added it to the quickwatch window and the following appears:

"DELETE 2CS.* FROM [2 Current Schedule] AS 2CS WHERE ( 2CS.[Job Run Date]=
#04/16/2005# ) AND ( 2CS.[Job Start Time] >= #07:00:00# ) AND ( 2CS.[Job
Start Time] <= #23:59:59# )"

Explicit date value must be in "mm/dd/yyyy" format and enclosed in hashes
(#). Try:

strSQL4 = "DELETE 2CS.* " & _
" FROM [2 Current Schedule] AS 2CS " & _
" WHERE ( 2CS.[Job Run Date]= " & Format(dteDte,"\#mm/dd/yyyy\#")
& " ) " & _
" AND ( 2CS.[Job Start Time] >= #07:00:00# ) " & _
" AND ( 2CS.[Job Start Time] <= #23:59:59# )"

--
HTH
Van T. Dinh
MVP (Access)

Hi I have the following code:

dteDte = Date()

strSQL4 = "DELETE [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= " & dteDte & " ) "
& _
"AND (([2 Current Schedule].[Job Start Time])>=#07:00:00# And ([2
Current Schedule].[Job Start Time])<=#23:59:59#));"

I have tried every different possibility I can think of with quotes around
the varible dteDte and nothing seems to work. The odd thing is I can use the
same varible in a SELECT statement (with single quotes around the double
quotes) and it works fine.

 

[Van T. Dinh]

Sorry. The problem is the digit character as the first character of the
Alias (which I simply abbreviated your Table / Query name).

Try:

strSQL4 = "DELETE [2CS].* " & _
" FROM [2 Current Schedule] AS 2CS " & _
" WHERE ( [2CS].[Job Run Date]= " & Format(dteDte,"\#mm/dd/yyyy\#") &
" ) " & _
" AND ( [2CS].[Job Start Time] >= #07:00:00# ) " & _
" AND ( [2CS].[Job Start Time] <= #23:59:59# )"

BTW, just want to comment you know very well on how to post follow-up
question and what info. / details are needed by potential respondents.

 

[Andreas]

I would try the following:

dteDte = Date()

strSQL4 = "DELETE [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= #" & _
Format(dteDte,"dd-mmm-yyyy") & "#) " & _
"AND (([2 Current Schedule].[Job Start Time])>=#07:00:00# " & _
"And ([2 Current Schedule].[Job Start Time])<=#23:59:59#));"

Regards,
Andreas

 

[Guest]

Hi,

Both statements work (Van & Andreas) and the query starts (i.e. it says it
will delete 0 rows) then the following error occurs:

Run-time error 2342

A RunSQL action requires an argument consisting of an SQL ststement.

I would try the following:

dteDte = Date()

strSQL4 = "DELETE [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= #" & _
Format(dteDte,"dd-mmm-yyyy") & "#) " & _
"AND (([2 Current Schedule].[Job Start Time])>=#07:00:00# " & _
"And ([2 Current Schedule].[Job Start Time])<=#23:59:59#));"

Regards,
Andreas

Hi I have the following code:

dteDte = Date()

strSQL4 = "DELETE [2 Current Schedule].* " & _
"FROM [2 Current Schedule] " & _
"WHERE ((([2 Current Schedule].[Job Run Date])= " & dteDte & " ) "
& _
"AND (([2 Current Schedule].[Job Start Time])>=#07:00:00# And ([2
Current Schedule].[Job Start Time])<=#23:59:59#));"

I have tried every different possibility I can think of with quotes around
the varible dteDte and nothing seems to work. The odd thing is I can use the
same varible in a SELECT statement (with single quotes around the double
quotes) and it works fine.

 

[Van T. Dinh]

It is probably in your code after the SQL String, especially the RunSQL
statement.

Get the constructed SQL String and paste it into the SQL View of a new Query
to confirm that the SQL String works. Did you expect the SQL String to find
no Records to delete?

Post the relevant code before the SQL String and after the SQL String.

 

[Guest]

Hi Van,

This is what the strSql string is like from the debug window:

"DELETE [2CS].* FROM [2 Current Schedule] AS 2CS WHERE ( [2CS].[Job Run
Date]= #04/16/2005# ) AND ( [2CS].[Job Start Time] >= #07:00:00# ) AND (
[2CS].[Job Start Time] <= #23:59:59# )"

And it should be deleteing some records, not 0 records.

 

[Van T. Dinh]

The String looks fine. Try changing the SQL to:

"SELECT [2CS].*
FROM [2 Current Schedule] AS 2CS
WHERE ( [2CS].[Job Run Date]= #04/16/2005# )
AND ( [2CS].[Job Start Time] >= #07:00:00# )
AND ( [2CS].[Job Start Time] <= #23:59:59# )"

and run it through the Query Interface and see if the Query / SQL returns a
non-empty Recordset or not.

If it returns an empty Recordset and you expect non-empty, you will need to
modify the criteria.

Post your code after the SQL construction. There is another problem in you
code since you have an error after the SQL construction with RunSQL.

 

[Guest]

I Posted the SQL to a query and then changed the view to design view.

I removed the date part (left in the time part) and the query extracted the
right records.

Then I deleted the time part and left the date # 4/16/2005 # and the query
returns no results. I have also changed the date to # 16/4/2005 # and this
is no good either. There are heaps of records with this date so it should
extract them.

Hi Van,

This is what the strSql string is like from the debug window:

"DELETE [2CS].* FROM [2 Current Schedule] AS 2CS WHERE ( [2CS].[Job Run
Date]= #04/16/2005# ) AND ( [2CS].[Job Start Time] >= #07:00:00# ) AND (
[2CS].[Job Start Time] <= #23:59:59# )"

And it should be deleteing some records, not 0 records.

It is probably in your code after the SQL String, especially the RunSQL
statement.

Get the constructed SQL String and paste it into the SQL View of a new Query
to confirm that the SQL String works. Did you expect the SQL String to find
no Records to delete?

Post the relevant code before the SQL String and after the SQL String.

 

[Guest]

Hi Van,

Tried this and again the problem is with the date. It won't extract
anything even though there are plenty of records with this date. I'm
absolutely baffled. Even a Select all records with # 04/16/2005 # or
#16/04/2005 # does not work.

The String looks fine. Try changing the SQL to:

"SELECT [2CS].*
FROM [2 Current Schedule] AS 2CS
WHERE ( [2CS].[Job Run Date]= #04/16/2005# )
AND ( [2CS].[Job Start Time] >= #07:00:00# )
AND ( [2CS].[Job Start Time] <= #23:59:59# )"

and run it through the Query Interface and see if the Query / SQL returns a
non-empty Recordset or not.

If it returns an empty Recordset and you expect non-empty, you will need to
modify the criteria.

Post your code after the SQL construction. There is another problem in you
code since you have an error after the SQL construction with RunSQL.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

This is what the strSql string is like from the debug window:

"DELETE [2CS].* FROM [2 Current Schedule] AS 2CS WHERE ( [2CS].[Job Run
Date]= #04/16/2005# ) AND ( [2CS].[Job Start Time] >= #07:00:00# ) AND (
[2CS].[Job Start Time] <= #23:59:59# )"

And it should be deleteing some records, not 0 records.

 

[Andreas]

1) Verify that the data type for the field is type Date.
2) If so, use the Format property in the table to (temporarily) apply
the format dd-mmm-yyyy and check your data - it is not uncommon for the
century part to go wrong resulting in the data being 1905 rather than 2005.
3) Whilst this is more of a personal preference, I will ONLY use dates
in the dd-mmm-yyyy format when used as criteria in queries - Access will
always convert this correctly and it leaves no room for mistakes when
debugging - see your attempts at dd/mm and mm/dd.

Regards,
Andreas

Hi Van,

Tried this and again the problem is with the date. It won't extract
anything even though there are plenty of records with this date. I'm
absolutely baffled. Even a Select all records with # 04/16/2005 # or
#16/04/2005 # does not work.

:

The String looks fine. Try changing the SQL to:

"SELECT [2CS].*
FROM [2 Current Schedule] AS 2CS
WHERE ( [2CS].[Job Run Date]= #04/16/2005# )
AND ( [2CS].[Job Start Time] >= #07:00:00# )
AND ( [2CS].[Job Start Time] <= #23:59:59# )"

and run it through the Query Interface and see if the Query / SQL returns a
non-empty Recordset or not.

If it returns an empty Recordset and you expect non-empty, you will need to
modify the criteria.

Post your code after the SQL construction. There is another problem in you
code since you have an error after the SQL construction with RunSQL.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

This is what the strSql string is like from the debug window:

"DELETE [2CS].* FROM [2 Current Schedule] AS 2CS WHERE ( [2CS].[Job Run
Date]= #04/16/2005# ) AND ( [2CS].[Job Start Time] >= #07:00:00# ) AND (
[2CS].[Job Start Time] <= #23:59:59# )"

And it should be deleteing some records, not 0 records.

 

[Gary Walter]

Hi GLT,

PMFBI

It sure sounds like your [Job Run Date]
also contains a time value other than midnight
(which is what #4/16/2005# implies)

easy enough to check...does this work
as you expect?

SELECT [2CS].*
FROM [2 Current Schedule] AS 2CS
WHERE
(
[2CS].[Job Run Date]>= #04/16/2005#
AND
[2CS].[Job Run Date]< #04/17/2005#
)
AND ( [2CS].[Job Start Time] >= #07:00:00# )
AND ( [2CS].[Job Start Time] <= #23:59:59# )

Apologies again for butting in.

Good luck,

GLW

Hi Van,

Tried this and again the problem is with the date. It won't extract
anything even though there are plenty of records with this date. I'm
absolutely baffled. Even a Select all records with # 04/16/2005 # or
#16/04/2005 # does not work.

The String looks fine. Try changing the SQL to:

"SELECT [2CS].*
FROM [2 Current Schedule] AS 2CS
WHERE ( [2CS].[Job Run Date]= #04/16/2005# )
AND ( [2CS].[Job Start Time] >= #07:00:00# )
AND ( [2CS].[Job Start Time] <= #23:59:59# )"

and run it through the Query Interface and see if the Query / SQL returns
a
non-empty Recordset or not.

If it returns an empty Recordset and you expect non-empty, you will need
to
modify the criteria.

Post your code after the SQL construction. There is another problem in
you
code since you have an error after the SQL construction with RunSQL.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

This is what the strSql string is like from the debug window:

"DELETE [2CS].* FROM [2 Current Schedule] AS 2CS WHERE ( [2CS].[Job
Run
Date]= #04/16/2005# ) AND ( [2CS].[Job Start Time] >= #07:00:00# )
AND (
[2CS].[Job Start Time] <= #23:59:59# )"

And it should be deleteing some records, not 0 records.

 

[Van T. Dinh]

It sounds to me that the [Job Run Date] is not of Date/Time data type or you
have non-zero time component in the values.

Check the Design view of the Table and make sure it is of Date/Time data
type.

As a test, set the format to "dd/mm/yyyy hh:nn:ss". Open the DataSheetView
of the Table and check whether you have non-zero time component in [Job Run
Date]. This can happen if you set the Default Value by Now() or assigning
by code using Now() or similar.

If you do have non-zero time component, change the criterion to:

( DateValue([2CS].[Job Run Date]) = #04/16/2005# )

for Table with small number of Records, says, less than 1000. Otherwise, it
is more efficient to use:

( [2CS].[Job Run Date] >= #04/16/2005#) AND
( [2CS].[Job Run Date] < #04/17/2005# )