Solved

How do I code this simple SQL in VB6.0

Posted on 2001-07-11
12
1,109 Views
Last Modified: 2008-02-26
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)

0
Comment
Question by:abrusko
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6274176
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
 
LVL 11

Expert Comment

by:arana
ID: 6274198
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
 
LVL 2

Author Comment

by:abrusko
ID: 6274229
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
DavidLester earned 75 total points
ID: 6274266
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
 
LVL 1

Expert Comment

by:sadcock
ID: 6274268
Andy-

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

-Scott
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6274273
"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
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6274291
You might need to put "dd" instead of dd, can't remember syntax, not near any books or anything.
0
 
LVL 2

Author Comment

by:abrusko
ID: 6274297
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6274312

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

That should do it...
0
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6274314
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
 
LVL 2

Author Comment

by:abrusko
ID: 6274320
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
 
LVL 2

Author Comment

by:abrusko
ID: 6274383
Thanks, David.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question