Problem with Datareport and sql

[Drivenn]

Hi,

My problem is vb6 datareport. I have dataenvironment and it's connected to access.
My form has two textboxes Text1 and Text2. this is meant to show reports between two dates (text1-text2) but it
doesn't do that. It shows report but there is now data in it but there should be.
Date-properties in datareport is correct.
If somebody knows what's wrong I'd like to know. Thanks

Tables are orders, customers, date


Private Sub Command1_Click()
On Error GoTo Err

DataEnvironment1.rsCommand1.Source = "Select * FROM Orders WHERE Date BETWEEN FORMAT(Date,'dd.mm.yyyy')='" & Text1.Text & "'AND FORMAT (Date,'dd.mm.yyyy')='" & Text2.Text & "' Order by CUSTOMERS"

DataEnvironment1.rscommand1.Open
rptReport.Show
Unload Me
Exit Sub

Err:
DataEnvironment1.rscommand1.Close
DataEnvironment1.rsCommand1.Source = "Select * FROM Orders WHERE Date BETWEEN FORMAT(Date,'dd.mm.yyyy')='" & Text1.Text & "'AND FORMAT (Date,'dd.mm.yyyy')='" & Text2.Text & "' Order by CUSTOMERS"
End Sub

 

[johnwm]

Are your database dates stored as strings or as dates? The Between operator only works (in this context) on data stored as dates.

If it is stored as dates then convert your strings using CDate. You may find it tidier to use DTPicker controls rather than Textboxes to ensure that you only get legal dates properly formatted.

If Date is the name of the date field in your database you will need to enclose it in square brackets in the query string. Your query may finish up looking like this:

"Select * FROM Orders WHERE [Date] BETWEEN #" & _
dtPicker1.Value & "# AND #" _
dtPicker2.Value & "# Order by CUSTOMERS"

In general try to avoid using reserved words as database field names or as variable names

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Drivenn]

Johnvm, Thanks for your tips.

Dates are stored as dates in database. I have now two dtpickers. I'd Try that query you gave me and I think my problem now is CDate. I Can't get it work proberly. How You Convert Stings with CDate in correct? When I try to run the program and I get an error like this:

Syntax error in date in query expression '[Date] BETWEEN
#27.3.2004# AND #30.3.2004#'.

 

[johnwm]

Which International settings are you using? Normally a DTPicker will return a Date type which doesn't require a CDate conversion. Can we also confirm that Date is the name of your database field?

If you're using ADO then the # signs aren't required and your query should read:

"Select * FROM Orders WHERE [Date] BETWEEN " & _
dtPicker1.Value & " AND " _
dtPicker2.Value & " Order by CUSTOMERS"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Drivenn]

I'm using uk settings. No Date isn't the name of database field. Name is Pvm. But now when I'm leaving those # I get error like this:

syntax error in number in query expression 'Pvm BETWEEN 27.3.2004 AND 30.3.2004'.

Thanks again!

 

[johnwm]

I suspect that 27.3.2004 is not recognised as a date.

Sorry I was only looking at the query. I haven't tried to set the DataEnvironment1.rsCommand1.Source directly, as I always use Parameter queries.

For this, my Command1.CommandText would look like:

SELECT * FROM Orders WHERE (pvm between input1 and input2)

and my Click event would look something like:

Private Sub Command1_Click()
Dim strParam1 as String
Dim strParam2 as String
strParam1 = CStr(DTPicker1.Value)
strParam2 = CStr(DTPicker2.Value)
With DataEnvironment1
If .Connection1.State <> 0 Then .Connection1.Close
.Connection1.Open
.Command1 (strParam1), (strParam2)
End With
rptReport.Show
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Drivenn]

I put that (SELECT * FROM Orders WHERE_
(pvm between input1 and input2) into a Command1.CommandText and now when I try to run the program
errorlooks like this:

compile error:

wrong number of arguments or invalid property assignment.

Pointer goes to rows Private Sub Command1_Click() and .Command1

 

[RoyVidar]

Hi!

I'm more from the Access world, but use the same Date format as you do. When doing vba sql, I've found there's a need to format the dates in a way that's understood by the database:

[tt]"Select * FROM Orders WHERE [Date] BETWEEN '" & _
format$(dtPicker1.Value, "yyyy-mm-dd") & "' AND '" _
format$(dtPicker2.Value, "yyyy-mm-dd") & "' Order by CUSTOMERS"[/tt]

Syntax like this works for me when connecting to MSDE from Access (hash (#) when connecting to Access). Perhaps some variations over that theme would work on your setup too?

Roy-Vidar

 

[johnwm]

When you type in the line:
.Command1
as soon as you type the next [space] you should see the Intellisense prompt:
Command1(Input1 as String,Input2 as String)

If you don't then check your DE in design mode. Check that the Command1 object is a child of Connection1 object. Check Command1 CommandType is 1 - adCmdText

Check in your form that the CommandButton is called Command1

This works just as is for me, so I'm not sure where to point you to next.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Drivenn]

Now it's working just like you said... I Had pvm in my command.commandtext and the correct word is Pvm... I'm stupid!
Thank you for your tips, now I can go further in my project!

I wonder if I want to put there an another param like customer. how it happens? Then I could print reports like 30.3.2004-30.5.2004 by one customer.

 

[johnwm]

Sure - just change your Command1.CommandText to look like:
[tt]
SELECT * FROM Orders WHERE (Pvm between input1 and input2) and Clientname like input3[/tt]

and in the Comman1_Click event add the green bits:
[tt]
....
Dim strParam2 as String
Dim strParam3 as String
strParam1 = CStr(DTPicker1.Value)
strParam2 = CStr(DTPicker2.Value)
strParam3 = myText.Text
With DataEnvironment1
If .Connection1.State <> 0 Then .Connection1.Close
.Connection1.Open
.Command1 (strParam1), (strParam2), (strParam3)
End With
....
[/tt]

where myText is the name of the Textbox with the clients name. You can also do wildcard matching with:

[tt]strParam3 = myText.Text & "%"
[/tt]

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Drivenn]

hi,

Johnvm, Thanks for your advices, now my program is working like it should in my computer.

when I install it to the other pc (win98) an try to use databases it says an error:

data error event hit err:
the microsoft jet database engine cannot open the file
it's already opened by another user, or you need permission to view its data.

I use Jet.OLEDB.4.0
I have used vb package and deployment wizard so it should be installed proberly and I have installed mdac-files and all adodc-objects have readwrite -rights. I think I have to set rights to somewhere but how?

Do you or anyone know how to solve this problem?

 

[johnwm]

New problem, therefore need new thread. As previously suggested please read faq222-2244 to find how to get the best from the forum

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'