Coding Problem (1 Viewer)

[ansentry]

I would like someone to have a look at this and tell me where I have gone wrong in the second procudure.


Thank you in advance





This works fine (I download the "SampleSearch.mdb" and modified it )

Private Sub cmdSearch_Click()
On Error Resume Next


Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "



If Me![StartDate] <> "" And EndDate <> "" Then
sCriteria = sCriteria & " AND Qry_DeliveryDateSearch.MyDate between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
End If


sSql = "SELECT DISTINCT [MyDate],[CustomerName],[Model],[SalespersonName],[ChassisNumber],[StockNum],[Colour],[Registration],[DeliveryTime],[VehiclesID] from Qry_DeliveryDateSearch " & sCriteria
Forms![Frm_DeliveryDateSearch]![frm_DeliveryDateSearchSubForm].Form.RecordSource = sSql

======SECOND PRODUCURE============

I created another form and however this one does not work, I thing it is the line,

sCriteria=sCriteria & " AND Qry_SalespersonSearch.SalespersonName"

( I just can't get my head around the different ways eg date, number or text)



Private Sub cmdSearch_Click()


Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "



If Me![CmboSalesperson] <> "" Then
sCriteria = sCriteria & " AND Qry_SalespersonSearch.SalespersonName"

End If


sSql = "SELECT DISTINCT [CustomerName],[Model],[MyDate],[SalespersonName],[ChassisNumber],[StockNum],[Colour],[Registration],[DeliveryTime],[VehiclesID] from Qry_SalespersonSearch " & sCriteria
Forms![Frm_SalespersonSearch]![Frm_SalespersonSearchSubForm].Form.RecordSource = sSql


End Sub


Regards,

 

[Jack Cowley]

Numbers:

"[Criteria] = " & Me.[MyNumber]

Text:

"[Criteria] = '" & Me.[MyText] & "'"

Date:

"[Criteria] = #" & Me.[MyDate] & "#"

hth,
Jack

 

[AncientOne]

your strCriteria doesn't make sense.

"WHERE 1=1"?

1 will always equal 1!!!!

What are you referring to here?

The Where clause is, I presume, meant to return records based on what is selected from the combo box. Therefore it should read

"WHERE 1 = 1 AND SalespersonName=' " & Me!CboSalesperson & "'"

It's obvious thar the 1=1 AND part is not doing anything!

"WHERE SalespersonName=' " & Me!CboSalesperson & "'" is enough,surely

NB-the re is a single quote then a double quote after= and two double quotes encloaing a single quote at the end.

 

[ansentry]

coding problem

AncientOne,

Thank you for your reply, I have changed the code as you suggested however when I select an item from the combo it return nothing.

I have attached a sample of the db. As I said in my first post I downloaded a sample db and copied from that.

Any help would be appreciated.


Regards

 

[ansentry]

coding problem

AncientOne,

Thank you for your reply, I have changed the code, as you suggested however when I select an item from the combo it return nothing.

I have attached a sample of the db. As I said in my first post I downloaded a sample db and copied from that.

Any help would be appreciated.

 

[ansentry]

Jack,

Thank you very much for this, I am sure it will help me in the future.


Regards,

 

[Jack Cowley]

Change this line of code:

sCriteria = "WHERE SalespersonName=' " & Me!cboSalesperson & "'"

to read like this:

sCriteria = "WHERE SalespersonName='" & Me!cboSalesperson & "'"

Remove the extra space after the first single quote before the first &

Jack

 

[AncientOne]

Yes, sorry, that was a typo on my part

 

[ansentry]

coding problem

To both AncientOne & Jack

A very big thank you . It now works fine.



Again thank you both,


Regards

 

[Jack Cowley]

You are welcome and continued success with your project...

Jack