Solved

How do I code this simple SQL in VB6.0

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

803 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