473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Breaking Out A Where Clause Via Code

Hello everyone,

I am reading a query's SQL string and adding criteria to that and then
resaving it. I know, might not be the best method, but it seems to
work and there are some pros to doing it this way for us.

However, there is a small issue. The code I am posting works if the
WHERE clause is a single line (meaning no OR's).

Can someone suggest how to modify the following code so that it
accounts if the WHERE clause has multiple "criteria" lines (meaning
OR's?)

Thanks

Set dbs = CurrentDb()
Dim lngI As Long
Dim strSQL As String
Dim strWhere As String
Dim strOrderBy As String
Dim strGroupBy
On Error Resume Next
strSQL = dbs.QueryDefs("qryHealthReopenedRevised").sql
strSQL = Left(strSQL, InStr(strSQL, ";") - 1)
strWhere = ""

lngI = InStr(strSQL, "WHERE ")
If lngI > 0 Then
strWhere = Mid$(strSQL, lngI)
strSQL = Left$(strSQL, lngI - 1)
' NEED TO ACCOUNT FOR OR's HERE and more than one since a criteria
might have multiple or lines
lngI = InStr(strWhere, "ORDER BY")
If lngI > 0 Then
strOrderBy = Mid$(strWhere, lngI)
strWhere = Left$(strWhere, lngI - 1)
End If
lngI = InStr(strWhere, "GROUP BY")
If lngI > 0 Then
strGroupBy = Mid$(strWhere, lngI)
strWhere = Left$(strWhere, lngI - 1)
End If
End If

Nov 13 '05 #1
1 1721
Rog
I can see you separating the main sql statement from the WHERE and
ORDER BY clauses, not adding criteria, but I suppose that is in a part
of the code you didn't post.

I don't see what the problem is, though. You would not have "multiple
lines" in an sql statement, just a longer WHERE clause, for instance
((WHERE field1 < 100 and field2 = true) or (field1 > 999 and field2 =
false)), which your code handles OK.

Can you give an example of what exactly is not working?

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: mr_burns | last post by:
Hi there, I have a text box that I will use to search a database. I would like to use it so that it will not use a whole string (ie. 'red striped top') but instead break it up into individual...
27
by: The Bicycling Guitarist | last post by:
Hi. I found the following when trying to learn if there is such a thing as a non-breaking hyphen. Apparently Unicode has a ‑ but that is not well-supported, especially in older browsers. Somebody...
22
by: stevenkobes | last post by:
If a word has a hyphen in it, IE will permit a line break at the hyphen, but Firefox/Mozilla won't. Apparently the Firefox behavior is standards-compliant, but it is not what I want. Is there a...
5
by: rettigcd | last post by:
I am using Access 2000. I have a query that I enter in sql that performs a left join: SELECT person.*,phone.* FROM person LEFT JOIN phone ON ((phone.person_id=person.person_id) AND...
26
by: GreatAlterEgo | last post by:
Hi, This is my query which is embedded in a COBOL program. EXEC SQL SELECT DATE, AGE, DURATION, AMT INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT FROM TAB1 WHERE CODE = :KEY.CODE AND...
49
by: elmar | last post by:
Hi Clers, If I look at my ~200000 lines of C code programmed over the past 15 years, there is one annoying thing in this smart language, which somehow reduces the 'beauty' of the source code...
3
by: Beowulf | last post by:
I have data coming from a telephony system that keeps track of when an employee makes a phone call to conduct a survey and which project number is being billed for the time the employee spends on...
2
by: Jim.Mueksch | last post by:
I am having a problem with using calculated values in a WHERE clause. My query is below. DB2 gives me this error message: Error: SQL0206N "APPRAISAL_LESS_PRICE" is not valid in the context where...
7
blyxx86
by: blyxx86 | last post by:
Does this break the normalization of tables?? I have the following tables: tblLocation LocationID LocationCode AttnTo Address1 Address2
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.