Geek asks and answers

Date range not working in vb with ms database

I need to update rows where pdate(date field) is not between the 1st and 20th days of the current month and year.

I use the code written below, but it gives an error saying "there are too few expected parameters 1". I am using MS Access 2007 as a database.

cn.Execute "update water set prel = (prel + (mmt * (tx / 100))) where pdate not between 1-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "  and 20-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & " "
+3
source share
2 answers

Your immediate problem was the lack of # date separators, as @MicSim showed you. However, I suggest you consider a different approach for your WHERE clause.

The WHERE clause after adding # delimiters looks like this (with today's date).

Debug.Print "WHERE pdate not between #1-" & _
    Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & _
    "# and #20-" & Format$(Now, "MMM") & "-" & _
    Format$(Now, "YYYY") & "#"
WHERE pdate not between #1-May-2011# and #20-May-2011#

, pdate . ( / .) , , - , pdate 20.05.2011 10:18:15 AM? WHERE . 20 ... , ?

, WHERE.

Debug.Print "WHERE pdate < " & _
    Format(DateSerial(Year(Date), Month(Date), 1), "\#yyyy-mm-dd#\") & _
    " OR pdate >= " & _
    Format(DateSerial(Year(Date), Month(Date), 21), "\#yyyy-mm-dd#\")
WHERE pdate < #2011-05-01# OR pdate >= #2011-05-21#

vb6. DateSerial(), Year(), Month(), Date() Format() . (. Microsoft ).

. @Brian Camire .

Debug.Print "WHERE pdate < " & _
    "DateSerial(Year(Date), Month(Date), 1)" & _
    " OR pdate >= " & _
    "DateSerial(Year(Date), Month(Date), 21)"
+6

, , . (#) ANSI 92 ADO/OLEDB:

cn.Execute "update water set prel = (prel + (mmt * (tx / 100))) 
where pdate not between 
    #1-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "#  
        and #20-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "# "

, yyyy-mm-dd hh:mm:ss yyyy-mm-dd .

+2

More articles:


All Articles