Search Form

[f430]

i have been trying to write a search code for a similar database, and i followed all the steps that were provided above, and my code was close to what lightning had but i have added date range in my search to search in between 2 dates, and i put this code in:

If Not IsNull(Me.date1) Then
        strWhere = strWhere & "([Date] >= " & Format(Me.date1, conJetDate) & ") AND "
    End If
       If Not IsNull(Me.date2) Then
        strWhere = strWhere & "([Date] < " & Format(Me.date2 + 1, conJetDate) & ") AND "
    End If

do you think that code is correct??


i am also having problems with the last step, where u do "DoCmd.Open Form "masterform ", , , strFilter"
i did that but i keep getting an error message, and when i go to debug this line is always highlighted.

i have a table called master form which has all my information, i made a form that is a duplicate to my master table in order for the last step to work, but i am not sure what is wrong.

Mary, i really hope you can help me with this, since you seem to be the only active member!!

i would appreciate any help on this.
Thanks

** Edit **
This post was a hijack of Search Form: multiple controls (Access 2003). I will split it across to its own thread. Please remember in future to create a new thread for your questions.

[Michael Adams]

f430

I am using a calendar control for searching between two separte dates to find information. Is this what you are looking for?
I, for instance, am using a combo box with the event of mousedown to bring up the calendar for both the from date and the to date. Is this something you would like to see?

[f430]

i am using a text box to enter the date as in mm/dd/yy.
but i dont think my code is working out.
so i think a calendar view would work a lot better.
i would appreciate some help with this coding for this calendar view

thanks

[NeoPa]

Hopefully, splitting this question into its own thread will give you a greater chance of getting any responses.

Good luck and welcome to Bytes!

[Michael Adams]

Here is what I found and that is working for me.
This is the code for the "From" combo box, the name I used was fromdatecmb and the calendar is named ocxcalendar

Private Sub fromdatecmb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    
    Set Originatorcmb = fromdatecmb
    
    ocxcalendar.Visible = True
    ocxcalendar.SetFocus
    If Not IsNull(Originatorcmb) Then
        ocxcalendar.Value = Originatorcmb.Value
    Else
        ocxcalendar.Value = Date
    End If
End Sub

Now here is the code I use for the todatecmb box

Private Sub todatecmb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set Originatorcmb = todatecmb
    
    ocxcalendar.Visible = True
    ocxcalendar.SetFocus
    If Not IsNull(Originatorcmb) Then
        ocxcalendar.Value = Originatorcmb.Value
    Else
        ocxcalendar.Value = Date
    End If
End Sub

Now the calendar needs to have focus:

 Private Sub ocxcalendar_Click()
    Originatorcmb.Value = ocxcalendar.Value
    Originatorcmb.SetFocus
    ocxcalendar.Visible = False
    Set Originatorcmb = Nothing
End Sub

There is a caveate to all of this, the field from the table you are pulling your data needs to be a text field or you will get a data type mismatch error. Also, you will need to make the calendar not visable in the properties of the calendar under design.

Check out this thread of mine where I asked for help with the same question. There is some great info from Neopa.

http://bytes.com/topic/access/answers/891805-using-dates-search-query-records

I hope all this helps.

MJA

[NeoPa]

Thanks Michael. I'm glad you pointed me back there, as I found an error in my post when I looked it over again :D Sorted now.

[f430]

Thanks Michael, this calendar view solved the issues i was having with the date.
my other question is, since the "from" and "to" dates are a combo box, can i just treat it as any combo box code for my search. because my search form contains 3 combo boxes and the date (2 additional combo boxes).
in other words can i just use this type of a code:

Dim strFilter As String
Dim varItem As Variant

strFilter = ""
strQt = Chr$(34)

If Not IsNull(Me!ProductName) Then strFilter = strFilter & "[Product ID]=" & strQt & "*" & Me!ProductName & "*" & strQt

however, im not exactly sure how to make it search in between these two dates?

[Michael Adams]

Where are you putting your from and to values in your if not isnull statement?

I was doing a cdount for my search and this is what it looked like:

EADRcount = DCount("[type]", "WorkOrderTracking", "[workedby] = 'MJA' And [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")

This code gave me a total count of all records for that date range.
[Type] = text field in the table
"WorkorderTrack ing" = the table
[workedby]='MJA' = search criteria limited to the employee matching MJA
between = operator used for the date range
"& me.fromdatecmb. value &" = the from value selected on the calendar
and = operator used for date range from 'and' to
"& me.todatecmb.va lue &" = the to value selected on the calendar

I hope this also helps.

[f430]

i was trying to get my search to filter a master table, which contains all the information such as date, part number,...and i am trying to make this date range one of the ways i could search through my master sheet.

[f430]

i am not really very familiar with this coding language, so i am not really sure how to make a code for a combo box for my search