simple ? for anyone besides me

[PJB]

I've got 2 masked edit boxes (mask = ##/##/####) i want the user to be able to input the dates and have it select all dates between and including the user inputted from my database

I can get it to work if i put the dates directly into the SELECT statement but not having any luck otherwise

[barrk]

If I understand what you are trying to do this may help. Get your input from the text boxes as variables, convert them, and insert them into your sql statement

dim date1 as string
dim date2 as string

date1 = text1.text 'your starting date
date2 = text2.text 'your ending date

sqlstring = "select * from mytable where date between " & format(date, date1) " and " & format(date, date2)

Hope this helps.

[PJB]

I've been trying something similiar to that and end up getting no records returned, but with your code i'm at least not getting an error so it's a start...

[barrk]

PJB Can you show me what your code looks like. I'd like to help if I can!

[PJB]

this code is what i'm using to dynamically set up different reports, as i say if i put the actual dates in my SQL statement it works fine, the mask on the masked edit boxes is ##/##/#### and in my database the PayDate field is set to short date

Private Sub cmdPrint_Click()
Dim DateFrom As String
Dim DateTo As String

DateFrom = mskFrom.Text
DateTo = mskTo.Text
sqlstring = "Select * from GEService Where PayDate Between " & Format(Date, DateFrom) & " and " & Format(Date, DateTo) & ""
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = sqlstring
        .Execute
    End With
    
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .Open cmd
    End With
   
   Dim q As Integer
   Dim intCtrl As Integer
   Dim x As Integer
   Dim z As Integer
   x = 0
   q = 0
   z = 0

   With DataReport6
   
   Set .DataSource = rs
   .DataMember = ""
   With .Sections("Section1").Controls
       For intCtrl = 1 To .Count
     If TypeOf .Item(intCtrl) Is RptLabel Then
                .Item(intCtrl).Caption = rs.Fields(q).Name & " :"
                 q = q + 1
            End If
            If TypeOf .Item(intCtrl) Is RptTextBox Then
                .Item(intCtrl).DataMember = ""
                .Item(intCtrl).DataField = rs(z).Name
                z = z + 1
            End If
    Next intCtrl
   End With
   .Refresh
   .Show
   End With
   Unload Me
End Sub

thanks for the help you've already given

[barrk]

Private Sub cmdPrint_Click()
Dim DateFrom As string
Dim DateTo As String

DateFrom = mskFrom.Text
DateTo = mskTo.Text
sqlstring = "Select * from GEService Where PayDate


*********
try adding tick marks before and after your dates

Between '" & Format(Date, DateFrom) & "' and '" &
Format(Date, DateTo) & "'"
*********






With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = sqlstring
.Execute
End With

[barrk]

One more thing.....

I was wrong to tell you to change the format to date. Since you are passing the value to a string you don't need to. Your code should look like this:

Private Sub cmdPrint_Click()
Dim DateFrom As String
Dim DateTo As String

DateFrom = mskFrom.Text
DateTo = mskTo.Text
sqlstring = "Select * from GEService Where PayDate Between '" & DateFrom & "' and '" & DateTo & "'"

try debug.print on sqlstring to see if you are getting a valid sql statement. I tried it and it works for me.

[PJB]

that gives me an error that says "data type mismatch in criteria expression"

[barrk]

Did you add the tick marks before and after the dates??

sqlstring = "Select * from GEService Where PayDate Between '" & DateFrom & "' and '" & DateTo & "'"

Just out of curiousity, what database are you extracting the data from?

[PJB]

yup i put the tick marks in, it's a Access 97 database the data type for this field is set to Date/Time and the format is set to shortdate

[barrk]

Hello PJB

If you haven't lost faith in me or found the answer yourself I think I've figured out what my problem was.

I had the format statement hosed up.

try this:

Dim DateFrom As String
Dim DateTo As String

DateFrom = mskFrom.Text
DateTo = mskTo.Text
sqlstring = "Select * from GEService Where PayDate Between '" & Format(DateFrom, "mm/dd/yy") & "' and '" & Format(DateTo, "mm/dd/yy") & "'"

You do still need the tick marks if you run this statement in SQL Server. Also you can substitute another date format if you require (i.e. "dd-mmm-yy")

[PJB]

with th tick marks it gives me the same dat type mismatch and without it gives me a blank form
I tried this

Select * From GEService Where Paydate Between #01/01/2000# And #12/25/2002#

and it works alright
i just tried changing my statement to

Dim DateFrom As String
Dim DateTo As String

DateFrom = mskFrom.Text
DateTo = mskTo.Text
sqlstring = "Select * from GEService Where PayDate Between '" & FormatDateTime(DateFrom, "mm/dd/yyyy") & "' and '" & FormatDateTime(DateTo, "mm/dd/yyyy") & "'"

and i just get an error that says Type Mismatch

[Edited by PJB on 10-04-2000 at 06:39 PM]

[barrk]

I'm sorry...I'm fresh out of ideas. Maybe there is someone who has more experience with Access that could help. All the changes work for me (I set my field data type to shortdate and tried to run it).

Hope you find an answer.

[PJB]

Thanx for everything, I'm sure I'll find out it's some other stupid setting that i have wrong

[barrk]

Try this:

Replace your tick marks with pound signs

sqlstring = "Select * from GEService Where PayDate Between #" & Format(DateFrom, "mm/dd/yyyy") & "# and #" &
Format(DateTo, "mm/dd/yyyy") & "#"

Give it a shot...what have you got to lose but more socks


[Edited by barrk on 10-04-2000 at 06:57 PM]

[PJB]

Have already tried that and got the error that said:
Run-time Error '13'

Type Mismatch

think i'll just set the computer on fire

[barrk]

Why don't we just call it quits. Stop looking for socks and have a beer. Gotta go till tomorrow

[PJB]

thanx again...

[shragel]

The Way to do this is as follows

code

Datefrom = Cdate("your date string)
Dateto = Cdate("Your Dateto String")

.....("Select * From ???? Where MYDATE > #" & Datefrom & "# AND < #" & Dateto & "#;")

/code

Any Question Feel Free To contact me at [email protected]

[PJB]

For anyone interested this is what i finally got to work:

Dim DateFrom As String
Dim DateTo As String

DateFrom = CDate(mskFrom.Text)
DateTo = CDate(mskTo.Text)
  sqlstring = "Select * from GEService Where PayDate Between #" & DateFrom & "# and #" & DateTo & "#"
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = sqlstring
        .Execute
    End With
    
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .Open cmd
    End With
   
   Dim q As Integer
   Dim intCtrl As Integer
   Dim x As Integer
   Dim z As Integer
   x = 0
   q = 0
   z = 0

   With DataReport6
   
   Set .DataSource = rs
   .DataMember = ""
   With .Sections("Section1").Controls
       For intCtrl = 1 To .Count
     If TypeOf .Item(intCtrl) Is RptLabel Then
                .Item(intCtrl).Caption = rs.Fields(q).Name & " :"
                 q = q + 1
            End If
            If TypeOf .Item(intCtrl) Is RptTextBox Then
                .Item(intCtrl).DataMember = ""
                .Item(intCtrl).DataField = rs(z).Name
                z = z + 1
            End If
    Next intCtrl
   End With
   .Refresh
   .Show
   End With
   Unload Me
End Sub

[barrk]

Glad to see you got it working! Sorry I couldn't solve it for you...got you close though.

[PJB]

Your help was greatly appreciated barrk!!!!