WHERE CLAUSE

  • Thread starter Thread starter JIMMIE WHITAKER
  • Start date Start date
J

JIMMIE WHITAKER

How would I get the following where clause to work? If I take out the last
part it works, it does the >= part. But I can't get it to work with the <=
part. This is in code, not query window. Also it's wrapped here, in code
the where clause is on one line only. Any help is appreciated.

Sub UPDT_FUEL()
Dim dbs As Database
Dim STRSQL2 As String, QUT As String
Dim T2, T4
T2 = Text2
T4 = Text4
Set dbs = CurrentDb
QUT = Chr$(34)
STRSQL2 = "UPDATE DISPATCH" _
& " SET DISPATCH.FL_SC = " & QUT & Me![Text10] & QUT _
& " WHERE DISPATCH.FL_SC Is Null AND DISPATCH.BILL_TO = 'C1057' AND
DISPATCH.LOAD_DATE >= " & T2 & " AND DISPATCH.LOAD_DATE <= " & T4
dbs.Execute STRSQL2
dbs.Close
Set dbs = Nothing
End Sub
 
change this line

DISPATCH.LOAD_DATE >= " & T2 & " AND DISPATCH.LOAD_DATE <= " & T4

into

DISPATCH.LOAD_DATE BETWEEN #" & T2 & "# AND #" & T4 & "#"

You still have to make sure T2 and T4 conforms to date format by checking

isDate(T2)
 
You still have to make sure T2 and T4 conforms to date format by checking

isDate(T2)
Click to expand...

You still have to check that T2 and T4 conform to date formats in a very
restricted list that is respected by Jet -- and they are not affected by
your regional settings.

In general, it is always safest to surround any user input, particularly of
dates, and manipulate them carefully. For example, here I would use a
couple of variables: this is the short version...


dim dtT2 as date, dtT4 as date
dim strWhere as String

' this uses regional settings so it's friendly to users
' but it will fail if T2 is null or empty or invalid
' therefore you need to trap for that too...
dtT2 = CDate(T2)
dtT4 = CDate(T4)

' okay, now use explicit formats to get these into
' SQL-compatible commands
' Use DateValue function to remove errors due to time
' components sneaking into the dates
strWhere = vbnewLine & "WHERE " & _
"DATEVALUE(Load_Date) <= " & Format$(dtT2, "\#yyyy\-mm\-dd\#") & _
vbNewLine & " AND " & _
Format$(dtT2, "\#yyyy\-mm\-dd\#") & " <= DATEVALUE(Load_Date)"

' Always check that you have got what you think you have got
MsgBox strWHERE

' and carry on here....


As indicated, you need to check for nulls and empty values but this should
give you an idea of how to go.

Hope it helps


Tim F
 
Why does the below work as a select query in code but a update query has to
be written differently?
In answer below all worked for an update, but the first code here listed
works for a select...
Just stumped as to why they can't be written the same way.
Dim strSQL
strSQL = "SELECT DISPATCH.TRIPNO, DISPATCH.BILL_TO, DISPATCH.SHIPPER,
DISPATCH.CITY_LD, DISPATCH.CITY_DEL, DISPATCH.CONSIGNEE, DISPATCH.LOAD_DATE,
DISPATCH.DEL_DATE, DISPATCH.BILLAT, DISPATCH.LOADNO, DISPATCH.B_FUEL,
DISPATCH.B_DROP, DISPATCH.B_LUMPER, DISPATCH.B_TOTAL, DISPATCH.B_NOTES,
DISPATCH.B_MILES, DISPATCH.FL_SC" _
& " FROM DISPATCH" _
& " WHERE DISPATCH.LOAD_DATE >= [Forms]![FIGURE_RATES]![Text2] And
DISPATCH.LOAD_DATE <= [Forms]![FIGURE_RATES]![Text4]" _
& " ORDER BY DISPATCH.BILL_TO, DISPATCH.SHIPPER, DISPATCH.CITY_LD,
DISPATCH.CITY_DEL;"
Forms![FIGURE_RATES]!DISPATCH1.Form.RecordSource = strSQL
 
Why does the below work as a select query in code but a update query
has to be written differently?
Click to expand...

I am not completely sure that I understand the question, but I think you
are getting at the problem with the parameters.

When running a query (any query, update or select) from the UI, Access will
intercept any UI-dependent functions and parameters and handle them for
you: that is why you can use VBA functions and Controls in the SQL. This
includes DoCmd.RunSQL, recordsources in forms and reports, and OpenQuery
and so on.

When running it from VBA, however, the SQL commands go straight to the db
engine without any expression evaluation taking place. The engine knows
absolutely nothing about [Forms]![FIGURE_RATES]![Text4] although it is at
least smart enough not to choke on the bang! marks. It treats them as
unknown parameters and returns a "Too few parameters provided" error.

You can get around this (a) by putting the actual values into the SQL code,
as I suggested in previous post, or (b) by manipulating the Parameters
collection of the querydef. Here is an example:

Set qdf=QueryDefs("qryGetAllAdolescents")
qdf.Parameters(1) = Date()
qdf.Parameters(2) = CDate([Forms]![FIGURE_RATES]![Text4])

qdf.Execute

and so on. There are advantages and drawbacks of each.

Hope that helps


Tim F
 
Back
Top