SQL String Format in VB6 Data Environment

[plcman]

Hi

I am using the data environment in VB6 to create reports based on a MS SQL Database. I am trying to pick items from the tables that fall on a particular date.

I use a date picker on a form to select the required date and import it into the data environment as a parameter (?).

The date picker supplies a date in the format dd/mm/yyyy.
The date in the db is a Date/Time Stamp hence the need for the format. Can anybody see what I am doing wrong.

SELECT Operators.Op_ID, Operators.Op_Name, Sessions.Produced, Sessions.Running_Time, Sessions.Stopped_Time, Sessions.Shift_No, Sessions.Start_Date, 'Format(Sessions.Start_Date, yyyymmdd)' AS Expr1 FROM Sessions INNER JOIN Operators ON Sessions.Operator_ID = Operators.Op_ID WHERE (Sessions.Shift_No = 3) AND ('Format(Sessions.Start_Date, yyyymmdd)' = ?)

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[JerryKlmns]

SELECT
O.Op_ID,
O.Op_Name,
S.Produced,
S.Running_Time,
S.Stopped_Time,
S.Shift_No,
S.Start_Date
FROM Sessions As S INNER JOIN Operators As O ON
S.Operator_ID = O.Op_ID
WHERE
(S.Shift_No = 3) AND
(Int(S.Start_Date)= Int(YourDatePicker.Value))

 

[plcman]

Hi

Thanks for the reply,
I get the same error message with that as well "Int is not a recognized built in function name"

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[JerryKlmns]

This ?

(Format(S.Start_Date, "yyyymmdd")= Format(YourDatePicker.Value, "yyyymmdd"))

 

[plcman]

Hi

No, same error message again

"Format is not a recognized built in function name"

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[JerryKlmns]

My last quess

(CONVERT(DATETIME, S.Start_Date, 103)= YourDatePicker.Value)

 

[plcman]

Hi

The code I was using ie ('Format(Sessions.Start_Date, yyyymmdd) etc works in VB when used as an sql string ie Sql_str="Select etc but when I put it in the Data environment it doesn't. I guess it is something to do with the use of quotes and brackets but my head is exploding now and I can't get it to work

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[SBerthold]

Looks like you may be using Jet and DAO (Database is an MDB file)?
Then you probably have an older version of the vbajet32.dll, or one of them. I think Format() came in a later version of Jet.

I remember several years ago that I used to get that exact same message on some machines that didn't have some of the Jet files current. vbajet32.dll was one of them.

Make sure everything is updated to VB6 Service Pack 6, and let us know what db you are connecting to.

Also, just to make sure there is nothing else wrong, please post the exact sql statement which was working for you in your vb code.

 

[JerryKlmns]

SBerthold

I am using the data environment in VB6 to create reports based on a MS SQL Database.

I saw it myshelf the 2nd time, that's why the CONVERT()...

 

[plcman]

Hi

Thanks for your replys.

I am using MS SQL Express, connecting thru SQL Native client.

The exact string I have working in VB Code is:

sql_str = "SELECT sum(Produced) as sum_prod, sum(Running_Time) as sum_run, sum(Stopped_Time) as sum_stop From Shifts WHERE Start_Date > '" & Format(dtpk_start_date.Value, "yyyymmdd") & "' AND Start_Date < '" & (Format(dtpk_end_date.Value, "yyyymmdd") + 1) & "'"


I am not sure what service pack I am updated to.


Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[JerryKlmns]

Have you seen this?

http://www.connectionstrings.com/article.aspx?article=whentousesqlnativeclient

 

[SBerthold]

>MS SQL Database

Thanks. Didn't see that (read it too fast!)

 

[gmmastros]

I see a couple issues.

1. For your start_date, you are using a 'greater than' condition. I recommend you change it to 'greater than or equal to'.

2. You are adding 1 to your end date, but you are doing this AFTER you format it. The format function returns a string. Since the string can be converted to a number, it does this before adding the 1. Most of the time, this is ok, but if you pick the last day of a month, you end up with the wrong value.

Ex: Suppose you pick Jan 31, 2007. After you format the string, you have 20070131. Then you add 1 and get 20070132 (which is not a valid date).

I recommend you change your code too....

sql_str = "SELECT sum(Produced) as sum_prod, sum(Running_Time) as sum_run, sum(Stopped_Time) as sum_stop From Shifts WHERE Start_Date >[!]=[/!] '" & Format(dtpk_start_date.Value, "yyyymmdd") & "' AND Start_Date < '" & (Format(dtpk_end_date.Value [!]+ 1[/!], "yyyymmdd")) & "'"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[plcman]

Thanks George, i hear what you are saying and take it on board. Unfortunately my problem comes when I try to use this sql in the VB Data environment SQL Builder. I got it working in VB but I can't get the formatting correct in the query builder.

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!