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
Solved

VB6 Data Report with Between dates SQL command question

Posted on 2002-03-18
13
604 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 70

Expert Comment

by:Éric Moreau
ID: 6876607
use single quote instead of #.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 6876850
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
ID: 6877122
# is for dates in Access, use' in SQL6.5 to 2000
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:bigfish777
ID: 6878039
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 70

Expert Comment

by:Éric Moreau
ID: 6878059
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
ID: 6878092
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
 
LVL 70

Expert Comment

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

Author Comment

by:bigfish777
ID: 6878158
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
ID: 6878770
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
ID: 6878808
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 70

Accepted Solution

by:
Éric Moreau earned 69 total points
ID: 6878889
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
ID: 6879190
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
ID: 6879193
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

828 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