Data Report

[n1a2v3i3n]

I want to generate data report through a Query. I have used the following code

Private Sub Command7_Click()

Dim Adodc1 As New ADODB.Recordset
Dim str As String
Dim st As String
Dim good As String
Dim a, b As Date
good = Chr(35)
st = "EC11"
a = Format(Date + 15, "dd/mm/yyyy")
b = Format(Date + 30, "dd/mm/yyyy")
MsgBox " Start date " & a
MsgBox " End Date " & b

str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Vechdetail.mdb;Persist Security Info=False"
st = " select Vehicle, insucom, invaf, invat from trafdetails where invat between " & good & a & good & "and" & good & b & good & ""
Adodc1.Open st, str, adOpenDynamic, adLockOptimistic

Do While Not Adodc1.EOF
MsgBox " Record Found " & Adodc1(0) & " " & Adodc1(1) & " " & Adodc1(2) & " " & Adodc1(3)
Adodc1.MoveNext
Loop

Set dr1.DataSource = Adodc1

dr1.Show
End Sub

Using above the record shows me all the record that are in the database instead of showing the selected records based on the query.

Please help me on how do i display the required records with help of query.

 

[SBerthold]

n1a2v3i3n,
Under this thread
thread222-1526740
I thought I told you not to use the Format$() function with formating when adding a date to a date variable!

You are doing it again! That may mess up the date on systems set different.

So change it to this:

a = Date + 15
b = Date + 30
MsgBox " Start date " & Format(a, "dd/mm/yyyy")
MsgBox " End Date " & Format(b, "dd/mm/yyyy")


Now, for an SQL Statement the date format must be in U.S. or ISO Format.

So:

WHERE invat BETWEEN " & Format$(a,"\#yyyy-mm-dd\#") & " AND " & Format$(b,"\#yyyy-mm-dd\#")

 

[SBerthold]

>I thought I told you not to use the Format$() function with formating when adding a date to a date variable!

1. Hard coded dates are done in U.S. Format
2. If the user enters the date in the system date format, then you can just use:

dtVar = CDate(TheDate)

If they enter it in some other format, set by your program and user instructions, than what is set in the system settings, (and we've been through this also) then you will need to Format the date prior to adding it to a date variable, into the System Date Format - which you will need to first figure out what it is!
You will just have alot more work, and confusion, to do as the programm gets bigger!

For SQL statements, as you have written above, you need to always format the dates to U.S./ISO Format first, as I show in my previous post, and never system date format.
You can use a Command Object with parameters and do not need to worry about formating the dates for an SQL-Command statement, as long as the dates are in date variables.

 

[SkipVought]

Dim a, b As Date

a = Format(Date + 15, "dd/mm/yyyy")
b = Format(Date + 30, "dd/mm/yyyy")

1. You have decalred a as a Variant and b as a date. In order to a & b as dates...

Dim a as date, b As Date

2. the Format function returns a STRING. Therefore...

Dim a as date, b As Date

a = Date + 15
b = Date + 30

is correct.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SBerthold]

Yes, the 'a' variable is a variant. I didn't spot this.
Though the OP obviously assumes it is getting set to a date variable, it isn't.

However, though should be declared as a date, it does not pose a difference to the problem, once changed straight to a = Date + 15
as I already did in my first post.

This is because it is still is handled as a date, for this example - debug.? Typename(a).
(Could pose problems if not handled carefully, such as easily assigning it to another type, or even an object.9

The greater problem is the usage in the SQL statement above.

This will not always work if the system date is set to something other than mm-dd-yyyy or yyyy-mm-dd,
contrary to what some may think.

Same holds true for decimal separators...

Only when the object used is local aware, or you use the objects own local aware functions that will convert 'TheDate' to the needed format will it possibly work.