Solved

VB6 Data Report with Between dates SQL command question

Posted on 2002-03-18
13
572 Views
Last Modified: 2008-02-01
Hi there,

I've got a really weird problem trying to create data reports generated with runtime SQL commands.  Pretty much the user opens a report form and can select the report type from a combo box, and then a frame becomes visible corresponding to the report type chosen.  Anyhow, for my Job date range report (simply choosing a Job number from a combo box, and specifying a start and end date), everything works fine if a significant date range is chosen...like a month or so.  I'm just in the development process so I don't have many records in my database.  For example, for Job 1, I have 2 records pertaining to work done, by different employees on a certain date.  Pretty much, I'm just accessing 2 tables, a Job table to get the job number, and a Labour table to get the work associated with that job number.  So a job can have many 'labours', but a 'labour' can be associated with only one job.

When the user hits a button to generate the date range report, my code is:

If cmdGenerate(4) Then
    devJobDateRange.Commands("Jobs").CommandText = "SHAPE {Select * from Job where Job.JobNumber = " & Me.cboJobNumber(1) & "}  AS Jobs APPEND ({Select * from Labour where Labour.Job = " & Me.cboJobNumber(1) & " and Labour.LabourDate Between #" & Me.txtJStartDate & "# and #" & Me.txtJEndDate & "# Order by Labour.LabourDate}  AS Labour RELATE 'JobNumber' TO 'Job') AS Labour"
    devJobDateRange.Commands("Jobs").Execute
    Load rptJobDateRange
    rptJobDateRange.Show
End If

Sorry if the formatting is all wild.  Basically, when the button is clicked, then I'm assigning a SQL statement to the command in my data environment, which contains 2 tables, a command to get Jobs (called "Jobs"), and a child command called "Labour".

Anyways, this all works fine apparently, because if I break and check the value of the statement right before the report is loaded, it reads:

SHAPE {Select * from Job where Job.JobNumber = 1}  AS Jobs APPEND ({Select * from Labour where Labour.Job = 1 and Labour.LabourDate Between #01/01/2002# and #12/31/2002# Order by Labour.LabourDate}  AS Labour RELATE 'JobNumber' TO 'Job') AS Labour

This is of course, if I specify Jan. 1 to Dec. 31, 2002.

My problem begins with narrowing down that date parameter.  It seems as though the smaller the date range the less accurate my resulting report is, which makes no sense.  For example, in my database for Job 1 I have only 2 records right now.  One for Feb. 3, and one for Mar. 8.  If I choose to grab everything from Jan. 1 to Mar. 1, for instance, it works fine and gets both.  If I narrow that to grabbing everything from Feb. 1 to Feb. 28, that works fine and grabs just the Feb. 3 record.  But say I want from Feb. 1 - Feb. 8 (one week), it screws up and gives me both records in the report.  So I tested the range and it doesn't work again, grabbing the only Feb. record, until I'm specifying Feb. 1 - Feb. 13!

So that doesn't work at all.  It gets even more strange because if I want to see the Mar. 8 record by itself, if I say give me everything from Mar. 1 - Mar. 31, it gives me both.  But if I say everything from Feb. 28 - Mar. 31, it gives me the correct record from Mar. 8.


Can somebody clue me in as to why this is so inconsistent and inaccurate - when I'm breaking and seeing the correct SQL statements being passed to the report each time?  It'd be much appreciated!  Sorry for the low points, I'm giving all I got.
0
Comment
Question by:bigfish777
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
use single quote instead of #.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Your dates are being interpreted as dd/mm/yyyy so 2/8/2002 is being read as 2 Aug 2002.  When you finally get to 2/13/2002 it knows it must be 13 Feb 2002.

You are reading your dates in as strings and they are being interpreted in the wrong format.
Solutions
1. Format your dates into date variables before putting them in the SQL

startdate = format(txtJStartDate,"dd/mm/yyyy")

2. Use a datepicker or calendar control instead of a text box for date entries.

good luck
mlmcc
0
 

Expert Comment

by:stevengann
Comment Utility
# is for dates in Access, use' in SQL6.5 to 2000
0
 

Author Comment

by:bigfish777
Comment Utility
Hi guys,

Thanks for the replies - however, I'm still having problems.

Simply changing from # around the dates to ' gives me the error 'Date type mismatch in query expression'

This is when I make the statement:

devJobDateRange.Commands("Jobs").CommandText = "SHAPE {Select * from Job where Job.JobNumber = " & Me.cboJobNumber(1) & "}  AS Jobs APPEND ({Select * from Labour where Labour.Job = " & Me.cboJobNumber(1) & " and Labour.LabourDate Between '" & Me.txtJStartDate & "' and '" & Me.txtJEndDate & "' Order by Labour.LabourDate}  AS Labour RELATE 'JobNumber' TO 'Job') AS Labour"


Taking your advice, mlmcc, it made sense.  But if I do:

Dim startDate As Date
Dim endDate As Date
   
startDate = Format(txtJStartDate, "dd/mm/yyyy")
endDate = Format(txtJEndDate, "dd/mm/yyyy")

devJobDateRange.Commands("Jobs").CommandText = "SHAPE {Select * from Job where Job.JobNumber = " & Me.cboJobNumber(1) & "}  AS Jobs APPEND ({Select * from Labour where Labour.Job = " & Me.cboJobNumber(1) & " and Labour.LabourDate Between '" & startDate & "' and '" & endDate & "' Order by Labour.LabourDate}  AS Labour RELATE 'JobNumber' TO 'Job') AS Labour"

This doesn't change how it was working before, still my original problem.  On a side note, I actually am getting the value from a calendar control.  I'm using an activex plugin called menushow, so I've got a text field and right beside it a small button, and when I hit the button it loads another form as a dropdown from the button with just a calendar on it (this is because I couldn't find a dropdown calendar control that would work).

So I just set the txtJStartDate value from the other form by:

frmReports.txtJStartDate = Me.Calendar1.Value


Anyways, ya.  No change if I make it into a date variable, and I get an error when I use single quotes.  Any other ideas?

btw - it's an Access 2000 database.


0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
oh Access!!!

Try this:

Dim startDate As Date
Dim endDate As Date
   
startDate = Format(txtJStartDate, "yyyy/mm/dd")
endDate = Format(txtJEndDate, "yyyy/mm/dd")

devJobDateRange.Commands("Jobs").CommandText = "SHAPE {Select * from Job where Job.JobNumber = " & Me.cboJobNumber(1)
& "}  AS Jobs APPEND ({Select * from Labour where Labour.Job = " & Me.cboJobNumber(1) & " and Labour.LabourDate
Between #" & startDate & "# and #" & endDate & "# Order by Labour.LabourDate}  AS Labour RELATE 'JobNumber'
TO 'Job') AS Labour"
0
 

Author Comment

by:bigfish777
Comment Utility
Hey,

That SQL command is the same as I had earlier, substituting Me.txtJStartDate for the date variable startDate, etc.  I've tried and it makes no change if I specify the text field or the variable, wrapping it with #.  I still screws up if I have a Feb. 3 record and try saying grab Feb. 1 - Feb. 8, for example.  It grabs all records until I say Feb. 1 - Feb. 13.  

Thanks for the comment.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
but have you formatted it yyyy/mm/dd ?
0
 

Author Comment

by:bigfish777
Comment Utility
Yes I copied and pasted your code snippet in.  Something weird though, if I break and check what startDate and endDate are as they are entered into the SQL statement they equal:

?startDate
01/02/2002

?endDate
08/02/2002

and the SQL statement is:

?devJobDateRange.Commands("Jobs").CommandText
SHAPE {Select * from Job where Job.JobNumber = 1}  AS Jobs APPEND ({Select * from Labour where Labour.Job = 1 and Labour.LabourDate Between #01/02/2002# and #08/02/2002# Order by Labour.LabourDate}  AS Labour RELATE 'JobNumber' TO 'Job') AS Labour

So the formatting to yyyy/mm/dd doesn't actually do anything.  

I can't understand why, if it's set in my application to dd/mm/yyyy, and in access to a date - why it can't figure out it's Feb. 1 - Feb. 8 I'm looking for, and not Feb. 1 to Aug. 2 as mlmcc thought it probably is doing.

I tried bypassing the text field altogether and putting frmReportsStartDateDDCal.Calendar1.Value as the value right in the SQL statement for the start date and same with the end, because when the user clicks a calendar day it just makes the form not visible, but that didn't help.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Check your default Windows date setting.  
Open CONTROL PANEL  -> REGIONAL SETTINGS  ->  DATE Tab

Set the short date format to match how you want to enter the dates.  

good luck
mlmcc
0
 

Author Comment

by:bigfish777
Comment Utility
It is set to dd/mm/yyyy.  (18/03/2002)  This is how I would like it, and how my application uses it.  I haven't had any problems using dates in visual basic with access until this data report where I'm trying to use the between clause.

0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 69 total points
Comment Utility
because database engine always think in US format first (which is mm/dd/yyyy).

be sure that your date converted to yyyy/mm/dd is february (use msgbox to display it).
0
 

Author Comment

by:bigfish777
Comment Utility
I got it!  I had to set the dates to mm/dd/yyyy as in:

startDate = Format(txtJStartDate, "mm/dd/yyyy")
endDate = Format(txtJEndDate, "mm/dd/yyyy")

The calendar control puts it in British style, so I just convert it to US, and Access likes the US style.  I figured it out by making a query in Access and looking at the SQL it generated and saw that it automatically put the dates I entered as mm/dd/yyyy.  So I entered British and it converted to US automatically.

Why Access couldn't convert it similarly by me sending a British short date at runtime, I don't know.  

Thanks everyone for your help.
0
 

Author Comment

by:bigfish777
Comment Utility
It was converting dd/mm/yyyy to mm/dd/yyyy that did the trick, not yyyy/mm/dd - but your suggestion about the US database engine tipped me off to the answer.  Thanks!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

744 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

13 Experts available now in Live!

Get 1:1 Help Now