Solved

How do I code this simple SQL in VB6.0

Posted on 2001-07-11
12
1,105 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
Comment Utility
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 10

Expert Comment

by:arana
Comment Utility
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
Comment Utility
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
 

Accepted Solution

by:
DavidLester earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Expert Comment

by:UncleMatt
Comment Utility
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
Comment Utility
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
Comment Utility

"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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, David.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now