Combine Statement (1 Viewer)

[kitty77]

How can I combine these into one statement? Msampleid1 or Msampleid2

DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]=" & "'" & SampleID & "'", , acNormal
DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid2]=" & "'" & SampleID & "'", , acNormal

 

[GPGeorge]

Try:

DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]=" & "'" & SampleID & "' OR [Msampleid2]=" & "'" & SampleID & "'", , acNormal

Since my ability to inspect double and single quotes is marginal, you may need to tweak that to get it correct.

 

[SHANEMAC51]

Since my ability to inspect double and single quotes is marginal, you may need to tweak that to get it correct.

Dim S1
'text
S1 = "[MSAMPLEID1]='s%1' OR [MSAMPLEID2]='s%1'"
S1 = Replace(S1, "s%1", SAMPLEID)
'long
S1 = "[MSAMPLEID1]=s%1 OR [MSAMPLEID2]=s%1"
S1 = Replace(S1, "s%1", SAMPLEID)
debug.print s1
DoCmd.OpenForm "TEXTSEND", acNormal, , S1, acFormEdit, acWindowNormal

 

[Pat Hartman]

@SHANEMAC51
Welcome aboard

I think you might be over complicating the problem. George's one line solution solves the problem. Unless you are getting paid by the lines of code you write, most of the time, one line will be better than eight.

 

[SHANEMAC51]

@SHANEMAC51
Welcome aboard

I think you might be over complicating the problem. George's one line solution solves the problem. Unless you are getting paid by the lines of code you write, most of the time, one line will be better than eight.

I don't get paid for lines, but I take care of my eyes and nerves
in the code editor, I try on a font of 14 points, it's about 100 characters

I really don't like it when a line of code is longer than 100 characters and doesn't fit into the screen
, so I break ALL long lines into substrings, and without a hyphen (preferably)

instead of a string 500-1000 characters received in the designer/editor requests
sw="select ...... from....... inner join ...... on ...... where ....... and ......."

I get several lines less than 100 characters long, and I don't use line margins often and no more than 1-2 positions
, I separate the code blocks with a line from """'
IT'S MUCH MORE READABLE

''''''''''''''''''''''''''''''' комментарий ''''''''''''''''''
Dim SW As String
SW = "select ...... "
SW = SW & " from....... "
SW = SW & " inner join ...... "
SW = SW & " on ...... "
SW = SW & " where ....... "
SW = SW & " and ......."
CurrentDb.Execute SW
'''''''''''''''''''''''''''''''''''''''''''''''

 

[SHANEMAC51]

How can I combine these into one statement? Msampleid1 or Msampleid2

DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]=" & "'" & SampleID & "'", , acNormal
DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid2]=" & "'" & SampleID & "'", , acNormal

""""""""' compare the readability of quotation marks """"""""""""""""

""""""" single line option """""""
DoCmd.Open form "TEXTSEND", normal, "", "[Msampleid1]=" & "'" AND Sample ID & "' OR [Msampleid2]=" & "'" AND Sample ID & "'", , Normal

""""""" option broken down into substrings """""""
Dim s1
'text '''''''''''''''''''''''''''''''''''''''''
S1 = "[M SAMPLE ID 1]='s%1' OR [MSAMPLEID2]='s%1'"
S1 = Replace(S1, "s%1", SAMPLEID)
debug.print s1
DoCmd.Open form "SENDING TEXT", acnormal, , S1, acformedit, acWindowNormal
''''''''''''''''''''''''''''

 

[June7]

Using multiple lines to incrementally build long string has its advantage but can be overdone.
Sometimes line continuation is good enough (BTW, continuation character is an underscore, not hyphen):

DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]='" & SampleID & _
                "' OR [Msampleid2]='" & SampleID & "'", , acNormal

 

[kitty77]

Try:

DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]=" & "'" & SampleID & "' OR [Msampleid2]=" & "'" & SampleID & "'", , acNormal

Since my ability to inspect double and single quotes is marginal, you may need to tweak that to get it correct.

Perfect!!

 

[June7]

Can eliminate two concatenations. See post 7.

 

[SHANEMAC51]

Can eliminate two concatenations. See post 7.

I do not understand the question
in paragraph 7 there are two options for one solution
- unreadable
- readable

 

[MajP]

you put your single quotes in their own concatenation

 & "'" &

June put hers in the leading and trailing concatenation

...'" & SampleID &"' Or...

 

[MajP]

If I Have a lot of delimiting to do I use the CSQL function to delimit strings, dates, nulls, booleans, numerics

DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]=" & csql(SampleID) & " OR [Msampleid2]=" & csql(SampleID), , acNormal

Or even add a variable

dim id as String
id = CSQL(me.sampleid)
DoCmd.OpenForm "TextSend", acNormal, "", "[Msampleid1]=" & ID & " OR [Msampleid2]=" & ID, , acNormal

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[June7]

How do you get Access VBA to recognize CSQL() function?

 

[MajP]

Its a UDF that i put in all my template DBs. Its in the link.

 

[GPGeorge]

""""""""' compare the readability of quotation marks """"""""""""""""

""""""" single line option """""""
DoCmd.Open form "TEXTSEND", normal, "", "[Msampleid1]=" & "'" AND Sample ID & "' OR [Msampleid2]=" & "'" AND Sample ID & "'", , Normal

""""""" option broken down into substrings """""""
Dim s1
'text '''''''''''''''''''''''''''''''''''''''''
S1 = "[M SAMPLE ID 1]='s%1' OR [MSAMPLEID2]='s%1'"
S1 = Replace(S1, "s%1", SAMPLEID)
debug.print s1
DoCmd.Open form "SENDING TEXT", acnormal, , S1, acformedit, acWindowNormal
''''''''''''''''''''''''''''

Readable?