Solved

How do I code this simple SQL in VB6.0

Posted on 2001-07-11
12
1,110 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…
Suggested Courses
Course of the Month3 days, 15 hours left to enroll

630 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