How do I code this simple SQL in VB6.0

Hi,

What is wrong with this code?  How should it be coded to select only records which have a "revised_date" earlier than today?

Thanks alot!!!

Andy (the code is below)
--------------------------
Dim DateHigh As Date
Dim SelectStatement As String

SelectStatement = "Select revised_date, scacc, minimum_charge, zipcode_low, zipcode_high " & _
      "from [FRT_CARRIER_MINIMUM_CHARGE] WHERE cstr(revised_date) < '" & DateHigh & "'"

Set rsMinChg = dbReference.OpenRecordset(SelectStatement)

LVL 2
abruskoAsked:
Who is Participating?
 
DavidLesterConnect With a Mentor Commented:
You can select all records which have a "revised_date" earlier than today using just the sql as below.  

I also notice that since you are using DAO that you are using MSAccess in which case you should enclose
the date using hashes "#".  You do not need to use hashes if you are using the SQL function Date() as
in the following example.


Beware that MSAccess may also change the date form around, e.g. DD/MM/YYYY may be swapped to MM/DD/YYYY so you will need to be carefull of this situation.


Dim SelectStatement As String

SelectStatement = "Select revised_date, scacc, minimum_charge, zipcode_low, zipcode_high " & _
     "from [FRT_CARRIER_MINIMUM_CHARGE] WHERE revised_date < Date()"

Set rsMinChg = dbReference.OpenRecordset(SelectStatement)

0
 
UncleMattCommented:
You can do this in one of 2 ways.

Datediff(dd, revised_date, '" & DateHigh & "') > 0

(or less than I can never remember)

or convert the date to yyyymmdd format

hopefully one of these ways will help.

Matt.
0
 
aranaCommented:
if revised_date is of date data type, then dont use cstr() as it will convert that to STRING TYPE and will not compare well against DateHigh (ou declared this one as a date type)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
abruskoAuthor Commented:
Hi guys...thanks for the replies.

I can't really convert the date to yyyymmdd format since I am comparing today (which I CAN convert to yyyymmdd) to a column in the table which is in mm/dd/yyyy format?

Matt, I used your suggestion:
------------------
SelectStatement = "Select scacc, minimum_charge, zipcode_low, zipcode_high " & _
      "from [FRT_CARRIER_MINIMUM_CHARGE] WHERE Datediff(dd, revised_date, '" & DateHigh & "') > 0"
------------------
but I got a runtime error #3061: Too few parameters, expected 1.

Any ideas?

Thanks again.
Andy
0
 
sadcockCommented:
Andy-

Is this against Access?  If so use the date quote character (#) to delimit the date you are comparing against.

-Scott
0
 
Arthur_WoodCommented:
"Select scacc, minimum_charge, zipcode_low, zipcode_high " & _
     "from [FRT_CARRIER_MINIMUM_CHARGE] WHERE DateHigh < #" & Date & "#"
 

if you are using ACCESS, and

"Select scacc, minimum_charge, zipcode_low, zipcode_high " & _
     "from [FRT_CARRIER_MINIMUM_CHARGE] WHERE DateHigh < '" & Date & "'"

if you are using SQL Server

Note that Access uses the # character to delimit dates, while SQL server uses the ' character.


 
0
 
UncleMattCommented:
You might need to put "dd" instead of dd, can't remember syntax, not near any books or anything.
0
 
abruskoAuthor Commented:
Thanks everyone...a few of the suggestions worked perfectly, but there is one point I need to make.  If you notice, I was not using date directly in the select statement, but was using datehigh instead.  That is because I will have other selections to do where I will want to check the date within some "window" (between datehigh and datelow, for example), so I need to know how to write the "select" statement using one of my variables rahter than date().  When I substitute datehigh in place of date() I get the error #3061: Too few parameters, expected 1.

Can someone help me with this...this is what I need to do...thanks you again!

Andy
0
 
Arthur_WoodCommented:

"Select scacc, minimum_charge, zipcode_low, zipcode_high " & _
    "from [FRT_CARRIER_MINIMUM_CHARGE] WHERE revised_date < #" & DateHigh & "#"

That should do it...
0
 
UncleMattCommented:
I'm taking it by the problems your having that the date stored is in string format.  If that is the case then you need to convert it to yyyymmdd using instr and mid$.

Because your dates will be order by month (Jan, Feb etc...)

if it is a date in the table then check the syntax for datediff.  then format your revised_date into the correct format that your database is expecting (Depending on your regional settings).

Matt.
0
 
abruskoAuthor Commented:
My mistake...I can use DATE() - NN in my Select...I should have known that...anyway, David Lester was the first to suggest the solution that did it for me...thanks David and thanks to everyone who helped!

David...I will accepst your proposed answer!

Thanks again,
Andy
0
 
abruskoAuthor Commented:
Thanks, David.
0
All Courses

From novice to tech pro — start learning today.