SQL problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 combo boxes that are linke to a list which displays records.
I also have two text boxes one is txtbegin and txtEnd. what i want to do is
in a command buton write a SQl to have the value in the combo box and the
start and end date be filtered together and sent to the record display
list(list4)

here is what i have it is wrong but it is a step in some direction....
please help me...

Me.list4.RowSource = "Select.....From....Where table.AreaOfLocation like '*"
& ME.Combo58 &"*' between forms!FinalDisplay!txtBegin And
forms!FinalDisplay!txtEnd"
(FinalDisplay) = page the text boxes are on [happens to be be same page]
(txtBegin and txtEnd ) = date text boxes
 
BIGRED56 said:
I have 3 combo boxes that are linke to a list which displays records.
I also have two text boxes one is txtbegin and txtEnd. what i want
to do is in a command buton write a SQl to have the value in the
combo box and the start and end date be filtered together and sent to
the record display list(list4)

here is what i have it is wrong but it is a step in some direction....
please help me...

Me.list4.RowSource = "Select.....From....Where table.AreaOfLocation
like '*" & ME.Combo58 &"*' between forms!FinalDisplay!txtBegin And
forms!FinalDisplay!txtEnd"
(FinalDisplay) = page the text boxes are on [happens to be be same
page] (txtBegin and txtEnd ) = date text boxes
Click to expand...

You're close, but we need to know the name of the date field in the
table that is supposed to be between txtBegin and txtEnd. Suppose, for
example, that the date field in the table is named "MyDateField". Then
your code could say something like

Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
"Forms!FinalDisplay!txtBegin And Forms!FinalDisplay!txtEnd"

The above should work (assuming you fill in the rest of it). Another
alternative is to build the values of the date limits into the SQL
statement; e.g.,

Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
Format(Me!txtBegin, "\#mm/dd/yyyy\#") & _
" And " & Format(Me!txtEnd, "\#mm/dd/yyyy\#")
 
hi thanks for your response.. but at the "And [Date Located] between" it says
Unexspected end of statement..


Dirk Goldgar said:
BIGRED56 said:
I have 3 combo boxes that are linke to a list which displays records.
I also have two text boxes one is txtbegin and txtEnd. what i want
to do is in a command buton write a SQl to have the value in the
combo box and the start and end date be filtered together and sent to
the record display list(list4)

here is what i have it is wrong but it is a step in some direction....
please help me...

Me.list4.RowSource = "Select.....From....Where table.AreaOfLocation
like '*" & ME.Combo58 &"*' between forms!FinalDisplay!txtBegin And
forms!FinalDisplay!txtEnd"
(FinalDisplay) = page the text boxes are on [happens to be be same
page] (txtBegin and txtEnd ) = date text boxes
Click to expand...

You're close, but we need to know the name of the date field in the
table that is supposed to be between txtBegin and txtEnd. Suppose, for
example, that the date field in the table is named "MyDateField". Then
your code could say something like

Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
"Forms!FinalDisplay!txtBegin And Forms!FinalDisplay!txtEnd"

The above should work (assuming you fill in the rest of it). Another
alternative is to build the values of the date limits into the SQL
statement; e.g.,

Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
Format(Me!txtBegin, "\#mm/dd/yyyy\#") & _
" And " & Format(Me!txtEnd, "\#mm/dd/yyyy\#")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
BIGRED56 said:
hi thanks for your response.. but at the "And [Date Located] between"
it says Unexspected end of statement..
Click to expand...

Please post your exact code, using copy/paste to make sure you don't
introduce any errors by retyping it.
 
OK,I figured it out thanks for the code... but i have just one more question
what coding should I put in to show all records when no date is putt into
text box

Dirk Goldgar said:
BIGRED56 said:
I have 3 combo boxes that are linke to a list which displays records.
I also have two text boxes one is txtbegin and txtEnd. what i want
to do is in a command buton write a SQl to have the value in the
combo box and the start and end date be filtered together and sent to
the record display list(list4)

here is what i have it is wrong but it is a step in some direction....
please help me...

Me.list4.RowSource = "Select.....From....Where table.AreaOfLocation
like '*" & ME.Combo58 &"*' between forms!FinalDisplay!txtBegin And
forms!FinalDisplay!txtEnd"
(FinalDisplay) = page the text boxes are on [happens to be be same
page] (txtBegin and txtEnd ) = date text boxes
Click to expand...

You're close, but we need to know the name of the date field in the
table that is supposed to be between txtBegin and txtEnd. Suppose, for
example, that the date field in the table is named "MyDateField". Then
your code could say something like

Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
"Forms!FinalDisplay!txtBegin And Forms!FinalDisplay!txtEnd"

The above should work (assuming you fill in the rest of it). Another
alternative is to build the values of the date limits into the SQL
statement; e.g.,

Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
Format(Me!txtBegin, "\#mm/dd/yyyy\#") & _
" And " & Format(Me!txtEnd, "\#mm/dd/yyyy\#")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
BIGRED56 said:
OK,I figured it out thanks for the code... but i have just one more
question what coding should I put in to show all records when no date
is putt into text box
[...]
Me.list4.RowSource = "Select.....From....Where " &
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
"AND MyDateField Between " & _
Format(Me!txtBegin, "\#mm/dd/yyyy\#") & _
" And " & Format(Me!txtEnd, "\#mm/dd/yyyy\#")
Click to expand...
Click to expand...

You've got two text boxes, txtBegin and txtEnd. Maybe you want it to
work so that having txtBegin empty means "all dates up to txtEnd",
having txtEnd empty means "all dates from txtBegin onward", and having
both empty means "all dates".

There are a couple of ways to do this. I might assemble a date criteria
string by interrogating each text box, like this:

Dim strDateCriteria As String

If Not IsNull(Me!txtBegin) Then
strDateCriteria = _
" AND MyDateField >= " & _
Format(Me!txtBegin, "\#mm/dd/yyyy\#")
End If

If Not IsNull(Me!txtEnd) Then
strDateCriteria = strCriteria & _
" AND MyDateField <= " & _
Format(Me!txtEnd, "\#mm/dd/yyyy\#")
' Note that we appended this criterion.
End If

Me.list4.RowSource = _
"Select.....From....Where " & _
"table.AreaOfLocation like '*" & ME.Combo58 &"*' " & _
strDateCriteria
 
Back
Top