Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

VB6 Data Report with Between dates SQL command question

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
bigfish777
Asked:
bigfish777
  • 6
  • 4
  • 2
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
use single quote instead of #.
0
 
mlmccCommented:
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
 
stevengannCommented:
# is for dates in Access, use' in SQL6.5 to 2000
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bigfish777Author Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
bigfish777Author Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
but have you formatted it yyyy/mm/dd ?
0
 
bigfish777Author Commented:
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
 
mlmccCommented:
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
 
bigfish777Author Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
bigfish777Author Commented:
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
 
bigfish777Author Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now