HOW TO INPUT AND/OR INTO A SQL WHERE CLAUSE FROM A COMBO BOX

D

Derick

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & [Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the second
alternative to work without success.
 
C

cjg.groups

Assuming your combo boxes are on a form, maybe you need to replace "Me"
with a more direct reference to the combos:

Forms!YourForm!ComboBox

I've never understood how VB knows what "Me" refers to at any given
time.
 
D

Duane Hookom

I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is this
from your SQL view? Have you played with it in design view?
 
D

Derick

Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and both work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc from a
value list with the idea that this would replace the "OR" in the first
version of the query that works as I want. The query understands the text
from the text boxes correctly but does not see the [Me]![COMBO51] input as OR
and I do not know what it sees but it returns every record not the desired
selection.

As to the <>False)) Access inserted that. I am not sure what it intended
either.

Derick
I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

Derick said:
THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the second
alternative to work without success.
Click to expand...
Click to expand...
 
D

Duane Hookom

You tell us where Combo51 appears in the query grid like under what column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Derick said:
Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc from a
value list with the idea that this would replace the "OR" in the first
version of the query that works as I want. The query understands the text
from the text boxes correctly but does not see the [Me]![COMBO51] input as
OR
and I do not know what it sees but it returns every record not the desired
selection.

As to the <>False)) Access inserted that. I am not sure what it intended
either.

Derick
I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

Derick said:
THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Derick

This is what appears in the SQL design screen:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%")) & Me!COMBO51 & (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

This appears in the top line of a column in the design grid:-

((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & [Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))

This appears in the first criteria line of the same colume:-

<>False

ProcessedDate, CardUser, TransactionDetails, Amount and SortCode each appear
in the first five columns of the design grid.

Regards Derick

Duane Hookom said:
You tell us where Combo51 appears in the query grid like under what column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Derick said:
Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc from a
value list with the idea that this would replace the "OR" in the first
version of the query that works as I want. The query understands the text
from the text boxes correctly but does not see the [Me]![COMBO51] input as
OR
and I do not know what it sees but it returns every record not the desired
selection.

As to the <>False)) Access inserted that. I am not sure what it intended
either.

Derick
I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Derick

Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" & Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like ""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode) ALike "%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike "%" &
[Me]![Text19] & "%" & [Me]![Combo51] & [CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And <=[Me]![Combo2] I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%" entered SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" & [Me]![Text19] & "%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for text19, OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51 (as OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%")) & " " & Me!COMBO51 & " " & (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " & ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow why. It
interprets the text input correctly but cannot interpret the Combo51 (as OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this as the
first time the reply crashed and I don't know if my reply got through.

Regards Derick

Regards Derick

Duane Hookom said:
You tell us where Combo51 appears in the query grid like under what column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Derick said:
Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc from a
value list with the idea that this would replace the "OR" in the first
version of the query that works as I want. The query understands the text
from the text boxes correctly but does not see the [Me]![COMBO51] input as
OR
and I do not know what it sees but it returns every record not the desired
selection.

As to the <>False)) Access inserted that. I am not sure what it intended
either.

Derick
I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

It's near impossible for me to figure this out without seeing your data and
understanding the values in all your Combo51 and Combo4 and Combo2 and...

Learn how to troubleshoot by clearing stuff from the criteria and viewing
the datasheet until you see records. This should provide a clue as to which
criteria isn't working.

--
Duane Hookom
MS Access MVP

Derick said:
Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" & Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like ""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode) ALike
"%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike "%" &
[Me]![Text19] & "%" & [Me]![Combo51] & [CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And <=[Me]![Combo2] I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%" entered SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" & [Me]![Text19] &
"%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for text19, OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51 (as OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) & " " & Me!COMBO51 & " " & (((CCandBank.ProcessedDate)>=Me!Combo0
And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " & ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow why.
It
interprets the text input correctly but cannot interpret the Combo51 (as
OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this as
the
first time the reply crashed and I don't know if my reply got through.

Regards Derick

Regards Derick

Duane Hookom said:
You tell us where Combo51 appears in the query grid like under what
column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Derick said:
Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc from
a
value list with the idea that this would replace the "OR" in the first
version of the query that works as I want. The query understands the
text
from the text boxes correctly but does not see the [Me]![COMBO51] input
as
OR
and I do not know what it sees but it returns every record not the
desired
selection.

As to the <>False)) Access inserted that. I am not sure what it
intended
either.

Derick

I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is
this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] &
"%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Derick

Hi Duane
Four typical records are as follows:-
There are 5 columns.

ProcessedDate CardUser TransactionDetails Amount SortCode
22/05/2006 3223 GATZBYS HAIR DESIGN HOWRAH -$18.36 Other CC
TRANSACTIONS
24/05/2006 2867 WOOLWORTHS W7014 ROSNY -$35.91 SMKT Woolworths
CC TRANSACTIONS
24/05/2006 2867 COLES ROSNY -$8.82 SMKT Coles CC
TRANSACTIONS
25/05/2006 3897 SORELL DEPT STORE SORELL -$18.36 Other CC
TRANSACTIONS


A search is intended to find records between certain ProcessedDates
(Provided by two datepicker calandars which put the selected dates
in Combo0 and Combo2)

AND WHERE the SortCode selected from Combo4) is "*SMKT*" for instance.

This I can do and it works fine.

Now within those records I want to limit the seach even more using
"*Text19*" AND/OR/OR NOT "*Text39*".
These limits are imposed on TransactionDetails field.
For example WHERE TransactionDetails Like "*RO*" OR (The OR is selected from
Combo51 value list) Like "*SO*".

If I use an SQL statement containing the OR it works fine. The problem
arises when I select the OR operator
from Combo51. I don't know how to put that selection into the SQL statement
so that it reads correctly.

This is what I am hoping to get help with.

Regards Derick





Duane Hookom said:
It's near impossible for me to figure this out without seeing your data and
understanding the values in all your Combo51 and Combo4 and Combo2 and...

Learn how to troubleshoot by clearing stuff from the criteria and viewing
the datasheet until you see records. This should provide a clue as to which
criteria isn't working.

--
Duane Hookom
MS Access MVP

Derick said:
Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" & Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like ""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode) ALike
"%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike "%" &
[Me]![Text19] & "%" & [Me]![Combo51] & [CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And <=[Me]![Combo2] I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%" entered SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" & [Me]![Text19] &
"%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for text19, OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51 (as OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) & " " & Me!COMBO51 & " " & (((CCandBank.ProcessedDate)>=Me!Combo0
And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " & ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow why.
It
interprets the text input correctly but cannot interpret the Combo51 (as
OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this as
the
first time the reply crashed and I don't know if my reply got through.

Regards Derick

Regards Derick

Duane Hookom said:
You tell us where Combo51 appears in the query grid like under what
column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc from
a
value list with the idea that this would replace the "OR" in the first
version of the query that works as I want. The query understands the
text
from the text boxes correctly but does not see the [Me]![COMBO51] input
as
OR
and I do not know what it sees but it returns every record not the
desired
selection.

As to the <>False)) Access inserted that. I am not sure what it
intended
either.

Derick

I'm not sure what [Me]![COMBO51] and <>False are doing in there. Is
this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] &
"%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Copy your WHERE clause into Word and format it with ()s on separate lines so
you can trouble-shoot.
--
Duane Hookom
MS Access MVP

Derick said:
Hi Duane
Four typical records are as follows:-
There are 5 columns.

ProcessedDate CardUser TransactionDetails Amount SortCode
22/05/2006 3223 GATZBYS HAIR DESIGN HOWRAH -$18.36 Other CC
TRANSACTIONS
24/05/2006 2867 WOOLWORTHS W7014 ROSNY -$35.91 SMKT
Woolworths
CC TRANSACTIONS
24/05/2006 2867 COLES ROSNY -$8.82 SMKT Coles CC
TRANSACTIONS
25/05/2006 3897 SORELL DEPT STORE SORELL -$18.36 Other CC
TRANSACTIONS


A search is intended to find records between certain ProcessedDates
(Provided by two datepicker calandars which put the selected dates
in Combo0 and Combo2)

AND WHERE the SortCode selected from Combo4) is "*SMKT*" for instance.

This I can do and it works fine.

Now within those records I want to limit the seach even more using
"*Text19*" AND/OR/OR NOT "*Text39*".
These limits are imposed on TransactionDetails field.
For example WHERE TransactionDetails Like "*RO*" OR (The OR is selected
from
Combo51 value list) Like "*SO*".

If I use an SQL statement containing the OR it works fine. The problem
arises when I select the OR operator
from Combo51. I don't know how to put that selection into the SQL
statement
so that it reads correctly.

This is what I am hoping to get help with.

Regards Derick





Duane Hookom said:
It's near impossible for me to figure this out without seeing your data
and
understanding the values in all your Combo51 and Combo4 and Combo2 and...

Learn how to troubleshoot by clearing stuff from the criteria and viewing
the datasheet until you see records. This should provide a clue as to
which
criteria isn't working.

--
Duane Hookom
MS Access MVP

Derick said:
Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" & Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like
""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but
generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode)
ALike
"%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike "%"
&
[Me]![Text19] & "%" & [Me]![Combo51] &
[CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And <=[Me]![Combo2]
I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%" entered
SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" & [Me]![Text19]
&
"%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for text19,
OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51 (as
OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) & " " & Me!COMBO51 & " " &
(((CCandBank.ProcessedDate)>=Me!Combo0
And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " & ((([CCandBank].[ProcessedDate])>=[Me]![Combo0]
And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow
why.
It
interprets the text input correctly but cannot interpret the Combo51
(as
OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this as
the
first time the reply crashed and I don't know if my reply got through.

Regards Derick

Regards Derick

:

You tell us where Combo51 appears in the query grid like under what
column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and
both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc
from
a
value list with the idea that this would replace the "OR" in the
first
version of the query that works as I want. The query understands the
text
from the text boxes correctly but does not see the [Me]![COMBO51]
input
as
OR
and I do not know what it sees but it returns every record not the
desired
selection.

As to the <>False)) Access inserted that. I am not sure what it
intended
either.

Derick

I'm not sure what [Me]![COMBO51] and <>False are doing in there.
Is
this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount,
CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%"
&
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%"
&
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount,
CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] &
"%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] &
"%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] &
"%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Derick

I have tried this many times even using the highlight tool to colour the
brackets to make sure that there is no odd one. I have increased the size of
the font also and used 3 line spacing so that it is easy to see the string.
All to no avail. I have tried using % instead of * and ALIKKE instead of LIKE
and single brackets instead of double brackets. I have even tried inserting a
space in double quotes either side of Me!Combo51. This didn't work either.
The query works if "OR" is typed in manually indicating that the string is
correct but not if I try to get the "OR" from the combo box (Combo51). You
can setup the query easily in the design grid by putting the same Date
criteria on the first 2 lines of the criteria in the ProcessedDate field put
the SortCode Crteria on the same 2 lines in the SortCode field and the Text19
on the first line and the Text39 on the second line in the TransactionDetails
field run this and enter the parameters manually when asked. Look at the SQL
view and then replace the OR word by Me!Combo51. Run it again and enter OR
when asked for Combo51's value. Now it doesn't work. WHY NOT???

[email protected] said:
Assuming your combo boxes are on a form, maybe you need to replace "Me"
with a more direct reference to the combos:

Forms!YourForm!ComboBox

I've never understood how VB knows what "Me" refers to at any given
time.

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And ((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" & Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & [Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%") And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the second
alternative to work without success.
Click to expand...
Click to expand...
 
D

Derick

Hi Duane

It seems like my last reply did not get through as it is not posted.
Basically it said that I tried using word some two weeks ago with no success.
I have tried other thigs too like multiple quotes and single and double
quotes etc. Have you any other ideas?

Derick

Duane Hookom said:
Copy your WHERE clause into Word and format it with ()s on separate lines so
you can trouble-shoot.
--
Duane Hookom
MS Access MVP

Derick said:
Hi Duane
Four typical records are as follows:-
There are 5 columns.

ProcessedDate CardUser TransactionDetails Amount SortCode
22/05/2006 3223 GATZBYS HAIR DESIGN HOWRAH -$18.36 Other CC
TRANSACTIONS
24/05/2006 2867 WOOLWORTHS W7014 ROSNY -$35.91 SMKT
Woolworths
CC TRANSACTIONS
24/05/2006 2867 COLES ROSNY -$8.82 SMKT Coles CC
TRANSACTIONS
25/05/2006 3897 SORELL DEPT STORE SORELL -$18.36 Other CC
TRANSACTIONS


A search is intended to find records between certain ProcessedDates
(Provided by two datepicker calandars which put the selected dates
in Combo0 and Combo2)

AND WHERE the SortCode selected from Combo4) is "*SMKT*" for instance.

This I can do and it works fine.

Now within those records I want to limit the seach even more using
"*Text19*" AND/OR/OR NOT "*Text39*".
These limits are imposed on TransactionDetails field.
For example WHERE TransactionDetails Like "*RO*" OR (The OR is selected
from
Combo51 value list) Like "*SO*".

If I use an SQL statement containing the OR it works fine. The problem
arises when I select the OR operator
from Combo51. I don't know how to put that selection into the SQL
statement
so that it reads correctly.

This is what I am hoping to get help with.

Regards Derick





Duane Hookom said:
It's near impossible for me to figure this out without seeing your data
and
understanding the values in all your Combo51 and Combo4 and Combo2 and...

Learn how to troubleshoot by clearing stuff from the criteria and viewing
the datasheet until you see records. This should provide a clue as to
which
criteria isn't working.

--
Duane Hookom
MS Access MVP

Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" & Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like
""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but
generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode)
ALike
"%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike "%"
&
[Me]![Text19] & "%" & [Me]![Combo51] &
[CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And <=[Me]![Combo2]
I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%" entered
SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" & [Me]![Text19]
&
"%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for text19,
OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51 (as
OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) & " " & Me!COMBO51 & " " &
(((CCandBank.ProcessedDate)>=Me!Combo0
And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " & ((([CCandBank].[ProcessedDate])>=[Me]![Combo0]
And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow
why.
It
interprets the text input correctly but cannot interpret the Combo51
(as
OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this as
the
first time the reply crashed and I don't know if my reply got through.

Regards Derick

Regards Derick

:

You tell us where Combo51 appears in the query grid like under what
column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and
both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT; etc
from
a
value list with the idea that this would replace the "OR" in the
first
version of the query that works as I want. The query understands the
text
from the text boxes correctly but does not see the [Me]![COMBO51]
input
as
OR
and I do not know what it sees but it returns every record not the
desired
selection.

As to the <>False)) Access inserted that. I am not sure what it
intended
either.

Derick

I'm not sure what [Me]![COMBO51] and <>False are doing in there.
Is
this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount,
CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%"
&
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%"
&
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount,
CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] &
"%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] &
"%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] &
"%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Pardon me for jumping in.

Unless you are building the SQL string on the fly using VBA, I know of no
way to insert operators (Or, And, NOT, <>, +, Like, etc) into the where
clause of an SQL statement.

SELECT ...
FROM ...
WHERE SomeField = "A" & " " & SomeField &
Forms!FormName!ComboComparisionOperator & " " & SomeOtherField = "BB"

You cannot pass "Or" or "And" as the value of
Forms!FormName!ComboComparisionOperator. As you know, that does not work.

You would need to use VBA to build the statement and then use that SQL. You
could change the SQL property of a stored (named) query.

You might be able to implement this in a very limited way. Generically, if
you were just using AND and OR as the choices for the combobox, you would
have SQL that looked something like the following.

SELECT ...
FROM ...
WHERE (SomeField = "A" AND SomeOtherField = "BB" AND
Forms!FormName!ComboComparisionOperator = "AND" )
OR ((SomeField = "A" OR SomeOtherField = "BB") AND
Forms!FormName!ComboComparisionOperator = "OR" )


Derick said:
Hi Duane

It seems like my last reply did not get through as it is not posted.
Basically it said that I tried using word some two weeks ago with no
success.
I have tried other thigs too like multiple quotes and single and double
quotes etc. Have you any other ideas?

Derick

Duane Hookom said:
Copy your WHERE clause into Word and format it with ()s on separate lines
so
you can trouble-shoot.
--
Duane Hookom
MS Access MVP

Derick said:
Hi Duane
Four typical records are as follows:-
There are 5 columns.

ProcessedDate CardUser TransactionDetails Amount
SortCode
22/05/2006 3223 GATZBYS HAIR DESIGN HOWRAH -$18.36 Other CC
TRANSACTIONS
24/05/2006 2867 WOOLWORTHS W7014 ROSNY -$35.91 SMKT
Woolworths
CC TRANSACTIONS
24/05/2006 2867 COLES ROSNY -$8.82 SMKT Coles
CC
TRANSACTIONS
25/05/2006 3897 SORELL DEPT STORE SORELL -$18.36 Other CC
TRANSACTIONS


A search is intended to find records between certain ProcessedDates
(Provided by two datepicker calandars which put the selected dates
in Combo0 and Combo2)

AND WHERE the SortCode selected from Combo4) is "*SMKT*" for instance.

This I can do and it works fine.

Now within those records I want to limit the seach even more using
"*Text19*" AND/OR/OR NOT "*Text39*".
These limits are imposed on TransactionDetails field.
For example WHERE TransactionDetails Like "*RO*" OR (The OR is selected
from
Combo51 value list) Like "*SO*".

If I use an SQL statement containing the OR it works fine. The problem
arises when I select the OR operator
from Combo51. I don't know how to put that selection into the SQL
statement
so that it reads correctly.

This is what I am hoping to get help with.

Regards Derick





:

It's near impossible for me to figure this out without seeing your
data
and
understanding the values in all your Combo51 and Combo4 and Combo2
and...

Learn how to troubleshoot by clearing stuff from the criteria and
viewing
the datasheet until you see records. This should provide a clue as to
which
criteria isn't working.

--
Duane Hookom
MS Access MVP

Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" &
Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like
""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but
generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode)
ALike
"%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike
"%"
&
[Me]![Text19] & "%" & [Me]![Combo51] &
[CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And
<=[Me]![Combo2]
I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%"
entered
SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" &
[Me]![Text19]
&
"%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for
text19,
OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51
(as
OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) & " " & Me!COMBO51 & " " &
(((CCandBank.ProcessedDate)>=Me!Combo0
And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " &
((([CCandBank].[ProcessedDate])>=[Me]![Combo0]
And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow
why.
It
interprets the text input correctly but cannot interpret the Combo51
(as
OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this
as
the
first time the reply crashed and I don't know if my reply got
through.

Regards Derick

Regards Derick

:

You tell us where Combo51 appears in the query grid like under what
column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and
both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT;
etc
from
a
value list with the idea that this would replace the "OR" in the
first
version of the query that works as I want. The query understands
the
text
from the text boxes correctly but does not see the [Me]![COMBO51]
input
as
OR
and I do not know what it sees but it returns every record not
the
desired
selection.

As to the <>False)) Access inserted that. I am not sure what it
intended
either.

Derick

I'm not sure what [Me]![COMBO51] and <>False are doing in
there.
Is
this
from your SQL view? Have you played with it in design view?
--
Duane Hookom
MS Access MVP

THIS WORKS AS I WANT:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount,
CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike
"%"
&
Me!Combo4
& "%")) OR (((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike
"%"
&
Me!Combo4
& "%"))
ORDER BY CCandBank.ProcessedDate;

THIS RETURNS EVERY RECORD:-

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount,
CCandBank.SortCode
FROM CCandBank
WHERE (((((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39]
&
"%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) &
[Me]![COMBO51]
& ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19]
&
"%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] &
"%")))<>False))
ORDER BY CCandBank.ProcessedDate;

I have been searching and reading for weeks now trying to get
the
second
alternative to work without success.
Click to expand...
Click to expand...
Click to expand...
 
D

Derick

Thank you John. I will try that using a Select Case construct to create the
appropriate AND or OR query where clause and build that into a query as you
suggest.

PS Thanks for jumping in

Regards
Derick

John Spencer said:
Pardon me for jumping in.

Unless you are building the SQL string on the fly using VBA, I know of no
way to insert operators (Or, And, NOT, <>, +, Like, etc) into the where
clause of an SQL statement.

SELECT ...
FROM ...
WHERE SomeField = "A" & " " & SomeField &
Forms!FormName!ComboComparisionOperator & " " & SomeOtherField = "BB"

You cannot pass "Or" or "And" as the value of
Forms!FormName!ComboComparisionOperator. As you know, that does not work.

You would need to use VBA to build the statement and then use that SQL. You
could change the SQL property of a stored (named) query.

You might be able to implement this in a very limited way. Generically, if
you were just using AND and OR as the choices for the combobox, you would
have SQL that looked something like the following.

SELECT ...
FROM ...
WHERE (SomeField = "A" AND SomeOtherField = "BB" AND
Forms!FormName!ComboComparisionOperator = "AND" )
OR ((SomeField = "A" OR SomeOtherField = "BB") AND
Forms!FormName!ComboComparisionOperator = "OR" )


Derick said:
Hi Duane

It seems like my last reply did not get through as it is not posted.
Basically it said that I tried using word some two weeks ago with no
success.
I have tried other thigs too like multiple quotes and single and double
quotes etc. Have you any other ideas?

Derick

Duane Hookom said:
Copy your WHERE clause into Word and format it with ()s on separate lines
so
you can trouble-shoot.
--
Duane Hookom
MS Access MVP

Hi Duane
Four typical records are as follows:-
There are 5 columns.

ProcessedDate CardUser TransactionDetails Amount
SortCode
22/05/2006 3223 GATZBYS HAIR DESIGN HOWRAH -$18.36 Other CC
TRANSACTIONS
24/05/2006 2867 WOOLWORTHS W7014 ROSNY -$35.91 SMKT
Woolworths
CC TRANSACTIONS
24/05/2006 2867 COLES ROSNY -$8.82 SMKT Coles
CC
TRANSACTIONS
25/05/2006 3897 SORELL DEPT STORE SORELL -$18.36 Other CC
TRANSACTIONS


A search is intended to find records between certain ProcessedDates
(Provided by two datepicker calandars which put the selected dates
in Combo0 and Combo2)

AND WHERE the SortCode selected from Combo4) is "*SMKT*" for instance.

This I can do and it works fine.

Now within those records I want to limit the seach even more using
"*Text19*" AND/OR/OR NOT "*Text39*".
These limits are imposed on TransactionDetails field.
For example WHERE TransactionDetails Like "*RO*" OR (The OR is selected
from
Combo51 value list) Like "*SO*".

If I use an SQL statement containing the OR it works fine. The problem
arises when I select the OR operator
from Combo51. I don't know how to put that selection into the SQL
statement
so that it reads correctly.

This is what I am hoping to get help with.

Regards Derick





:

It's near impossible for me to figure this out without seeing your
data
and
understanding the values in all your Combo51 and Combo4 and Combo2
and...

Learn how to troubleshoot by clearing stuff from the criteria and
viewing
the datasheet until you see records. This should provide a clue as to
which
criteria isn't working.

--
Duane Hookom
MS Access MVP

Hi D

I use the following code to generate the query.

Sub SQLStatement()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL
strSQL = "SELECT CCandBank.ProcessedDate, " & _
"CCandBank.CardUser, " & _
"CCandBank.TransactionDetails, " & _
"CCandBank.Amount, " & _
"CCandBank.SortCode "
strSQL = strSQL & " FROM CCandBank "
strSQL = strSQL & "WHERE CCandBank.ProcessedDate >=Me!Combo0 AND
CCandBank.ProcessedDate <= Me!Combo2 "
strSQL = strSQL & "AND CCandBank.SortCode Like ""%"" &
Me![Combo4] &
""%"" "
strSQL = strSQL & "AND CCandBank.TransactionDetails Like ""%"" &
Me!Text19 & ""%"" & Me!Combo51 & CCandBank.TransactionDetails Like
""%"" &
Me!Text39 & ""%"""
strSQL = strSQL & " ORDER BY CCandBank.ProcessedDate;"
Debug.Print strSQL
Set rs = CurrentDb.CreateQueryDef("Purchase", strSQL)

End Sub

It runs OK till the last line where it gives a typemismatch but
generates
the query anyway. This is what it put in the SQL design window

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE (((CCandBank.ProcessedDate)>=[Me]![Combo0] And
(CCandBank.ProcessedDate)<=[Me]![Combo2]) AND ((CCandBank.SortCode)
ALike
"%"
& [Me]![Combo4] & "%") AND (([CCandBank].[TransactionDetails] ALike
"%"
&
[Me]![Text19] & "%" & [Me]![Combo51] &
[CCandBank].[TransactionDetails])
ALike "%" & [Me]![Text39] & "%"))
ORDER BY CCandBank.ProcessedDate;

The Design Grid looks like this:-

Col 1 Field ProcessedDate Criteria >=[Me]![Combo0] And
<=[Me]![Combo2]
I
entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Criteria ALike "%" & [Me]![Combo4] & "%"
entered
SMKT
Col 6 Field [CCandBank].[TransactionDetails] ALike "%" &
[Me]![Text19]
&
"%"
& [Me]![Combo51] & [CCandBank].[TransactionDetails]
Criteria ALike "%" & [Me]![Text39] & "%" entered col for
text19,
OR
for combo51and wool for text39.

It returned no records.

Modifying the SQL window to select Date, sortcode, text19 Combo51
(as
OR)
Date, sortcode, text39 FINDS ALL 6000 plus records.

SQLlooks like

SELECT CCandBank.ProcessedDate, CCandBank.CardUser,
CCandBank.TransactionDetails, CCandBank.Amount, CCandBank.SortCode
FROM CCandBank
WHERE ((((((CCandBank.ProcessedDate)>=Me!Combo0 And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text39 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")) & " " & Me!COMBO51 & " " &
(((CCandBank.ProcessedDate)>=Me!Combo0
And
(CCandBank.ProcessedDate)<=Me!Combo2) And
((CCandBank.TransactionDetails)
ALike "%" & Me!Text19 & "%") And ((CCandBank.SortCode) ALike "%" &
Me!Combo4
& "%")))<>False))
ORDER BY CCandBank.ProcessedDate;


The Design Grid looks like this:-

Col 1 Field ProcessedDate I entered when prompted 1/1/05, 1/1/06
Col 2 Field CardUser
Col 3 Field TransactionDetails
Col 4 Field Amount
Col 5 Field SortCode Ientered SMKT
Col 6 Field ((([CCandBank].[ProcessedDate])>=[Me]![Combo0] And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text39] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%")) & " " &
[Me]![COMBO51] & " " &
((([CCandBank].[ProcessedDate])>=[Me]![Combo0]
And
([CCandBank].[ProcessedDate])<=[Me]![Combo2]) And
(([CCandBank].[TransactionDetails]) ALike "%" & [Me]![Text19] & "%")
And
(([CCandBank].[SortCode]) ALike "%" & [Me]![Combo4] & "%"))
Criteria <>False

It returned all 600 plus records.

The query seems to be interpreting Combo51 differently. I don'tknow
why.
It
interprets the text input correctly but cannot interpret the Combo51
(as
OR)
as a reserved SQL word.

This what I need help with. This is the second time I have sent this
as
the
first time the reply crashed and I don't know if my reply got
through.

Regards Derick

Regards Derick

:

You tell us where Combo51 appears in the query grid like under what
column?
The same with the <>False.

--
Duane Hookom
MS Access MVP

Hi
I have tried using Forms!YourForm!ComboBox and the Me! syntax and
both
work
so I use the shorter version.

The [Me]![COMBO51] is on a form that provides ;OR;AND;AND NOT;
etc
from
a
value list with the idea that this would replace the "OR" in the
first
version of the query that works as I want. The query understands
the
text
from the text boxes correctly but does not see the [Me]![COMBO51]
input
as
OR
and I do not know what it sees but it returns every record not
the
desired
selection.

As to the <>False)) Access inserted that. I am not sure what it
intended
either.

Derick
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL query to display values in a Combo box 2
expression in SQL works in control property but not in VBA 4
How to get a series of combo boxes to filter data on a continuous form? 4
how to use where clause in my query 5
problem with cascading combos A97 0
Access SQL conversion to Sprint DB Pro 0
Combo Box issue 3
Coding Problem in Combo Box 7

Top