code for filtering date field and wildcard search in the form

[Sangpo]

Hello,
1st Question : I have following codes written in access 2007 to display the items that iI select from Combo box named combo62. This works well.
But i am struct as to how to change code for displaying items .eg. to display all item begines with A,AE, B or BB ect
I tried this SQL = SQL & " where item = Like "" '" & Combo62 & "'" "*"
But donot wotk .
Please help me please.

Sub itemwise()
Dim LSQL As String
SQL = "select * from qc_logT"
SQL = SQL & " where item = '" & Combo62 & "'"
Form_ViewRecordF.RecordSource = SQL
End Sub

Private Sub Combo62_AfterUpdate()
itemwise
End Sub


2st Question : I have written following code for for displaying date of received

Sub receivedate()
Dim LSQL As Date
SQL = "select * from qc_logT"
SQL = SQL & " where item = '" & cbreceivedate & "'"
Form_ViewRecordF.RecordSource = SQL
End Sub

Private Sub cbreceivedate_AfterUpdate()
receivedate
End Sub

Here i get message as DAta mismatch

Please help me

Thaknk you all.
Sangpo

 

[Shado]

SQL = SQL & " where item = Like "" '" & Combo62 & "'" "*"

Try:
SQL = SQL & " where item = Like '" & Combo62 & "%'"

As for the dates, remember SQL likes to Americanize it.. It's YYYY-MM-DD.

 

[vbaInet]

1. For begins with:

SQL = SQL & " WHERE [COLOR=red][[/COLOR]item[COLOR=red]] LIKE[/COLOR] " & [COLOR=red]Chr(34)[/COLOR] & [COLOR=red]Me.[/COLOR]Combo62[COLOR=red].Value [/COLOR]& "[COLOR=red]*[/COLOR]" &[COLOR=red] Chr(34)[/COLOR]

Or

SQL = SQL & " WHERE [COLOR=red]Left([[/COLOR]item[COLOR=red]], 2)[/COLOR] [COLOR=red]=[/COLOR] " & [COLOR=red]Chr(34)[/COLOR] & [COLOR=red]Me.[/COLOR]Combo62[COLOR=red].Value[/COLOR] & [COLOR=red]Chr(34)[/COLOR]

Please note the syntax.

2. Data mismatch because you're passing the wrong data type to one of your fields. Dates need to be wrapped in "#".

 

[Sangpo]

Dear Sir,

Thanks for your resposne to my qureies. I have tried a lot as suggested by you. However my problems did not solved. For your reference and further suggestion, I haved uploaded the my pice of database . Please kindly make the correction. Thanks a lot
Im from Bhutan

 

[Sangpo]

Please refer to my statement where i have said I" i have uplaoded my pice of database. but seems not upload. CCould anyone tell me how to attach the data base?
sorry

 

[vbaInet]

I haven't yet requested for your database. I'm sure that you can make the changes yourself.

Show us the code that you tried.

 

[Sangpo]

Sir, Ok as suggested by you. Following are the codes I have tried

This is code wildcard searching from the text box. eg. If i enter A then all item with A must dispaly nad if I type AC then all item starts with ac be displayed. The displays are in the form. I have used the format of form as Cotiuous.
Sub wildcardtry()
Dim SQL As String
SQL = "select * from qc_logT"
'SQL = SQL & " where left[item],2)= like" & Chr(34) & Me.combo622.Value & "*" & Chr(34)
SQL = SQL & " where [item ]= like" & Chr(34) & Me.combo622.Value & "*" & Chr(34)
'SQL = SQL & " where item = like'" & combo622 & "%'"
Form_ViewRecordF.RecordSource = SQL
End Sub
Private Sub combo622_AfterUpdate()
wildcardtry
End Sub
Private Sub txtdate_AfterUpdate()
datewise
End Sub

This for displaying the selected date from combo box
Sub datewise()
Dim SQL As String
SQL = "select * from qc_logT"
SQL = SQL & " where resultReceivedate = '" & txtdate & "'"
Form_ViewRecordF.RecordSource = SQL
End Sub

Thank you
sangpo

 

[vbaInet]

Please look carefully at my code again. You are making the same mistake again. Pay close attention to all the operators used and not used.

 

[Sangpo]

sir, thanks. you a lot. now it works perfectly for wildcard search.. I have not type the correct code that you have suggested. Thanks u so much.

But my second problem is that.

name of field is= resultreceivedate . Type is date.
name of combo box is =txtdate

purpose is= to display data agains date that i have choonsen from combo box ( dropdorwon.
following is code.

Sub datewise()
Dim SQL As date
SQL = "select * from qc_logT"
SQL = SQL & " where resultReceivedate = '" & txtdate & "'"
Form_ViewRecordF.RecordSource = SQL
End Sub

Private Sub txtdate_AfterUpdate()
datewise
End Sub

Error message i get = mismatch data type.
formate of date is mm:dd:yy

Thank you you once gain
sangpo

 

[vbaInet]

Ok, remember that for strings you use the single quote (in your example), but for dates you need this --> #

 

[Sangpo]

Hi , sir ((VbaInet) Hoooree. It does work well. Thank you.


With this idea, I want to filter data from Date to Date with foll0wing code but not working. please



Sub fromdatetodate()
Dim SQL As String
SQL = "select * from qc_logT"
'SQL = SQL & " where fromdate = #" & txtdate & "#" And "where todate= #" & txtdate & "#"
SQL = SQL & " where fromdate = #" & txtdate & "#" And "where todate= #" & txtdate & "#"
Form_ViewRecordF.RecordSource = SQL
End Sub

Sangpo
Bhutan

+17609191

 

[vbaInet]

We're back to the same mistake again. Again, please look carefully at what I wrote.

 

[Sangpo]

Sir, my two problems solved. ie wild card seach and single date type data. I use # instead of single quoate and my problems solved. But my another problems for 2 dates. ie. I want filtter from date and to date. where we used to write in qurey design like , between date..And to date somthething like. Simlarly, with the idea you gave me applied here with following code: butdoest not work. I cant think of any. please highling on this please.

field name is= datesent(date type)
combo1=cbFromdate
combo2=cbTodate

SQL = SQL & " where datesent = Between #" & cbfromdate & "#" And "where datesent= #" & cbTodate & "#"

eroor mesaage i get is=Type mismatch

 

[vbaInet]

Like I said you were making the same old mistake:

SQL = SQL & " WHERE datesent BETWEEN #" & cbfromdate & "# AND #" & cbTodate & "#"

Look at the syntax of the BETWEEN condition:

http://www.techonthenet.com/sql/between.php/