Re: Need to use 2 fields in link criteria

From: Sandra Daigle (Invalid_at_KeepYourSpam.org)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 12:49:17 -0400

I meant to also say that all of that was simply to get the final closing
quote around SSN so that the full SQL string has the SSN as a literal
string.

If you break down the whole thing, the pieces are:

1) stLinkCriteria =
2) "EntryNum="
3) Me![EntryNum]
4) " AND SSN="""
5) me.SSN
6) """"

Look at the 4th line and you'll see that there are also 4 quotes there but
it is more evident that the first and fourth are VBA string delimiters since
there is intervening text within the quotes.

Hope this helps! 

-- 
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.
Sandra Daigle wrote:
> Hi Bonnie,
>
> The two sets after SSN are actually the following:
>
> - First quote - VBA String Delimiter
>
> - Second and Third quotes - Doubled up to convert to single to leave
> in final string
>
> - Fourth quote - VBA String Delimiter
>
> It looks like a mess but just like with parentheses in a math
> equation, you eventually get the hang of it and learn to work with it
> in small pieces first then put it all together.
>
>
> Bonnie wrote:
>> Sandra,
>>
>> Thank you for the wordage and a special thanks for the
>> explanation you included. It was so well put that light
>> bulbs came on!!! I think I'm beginning to understand. I
>> am confused on one point: Why add one set of double
>> quotes before & me.SSN & and two sets after? (Yes, my SSN
>> field is text to avoid losing leading zeros so I need the
>> extra quotes.) Thanks again.
>>
>>> -----Original Message-----
>>> Hi Bonnie,
>>>
>>> Basically you just the second condition just like the first except
>>> that you need to join the two conditions with the AND operator.
>>>
>>> stLinkCriteria = "[EntryNum]=" & Me![EntryNum] & " AND SSN=" &
>>> me.SSN
>>>
>>> Note that the AND is inside of a VB string and the reference to the
>>> SSN control is outside of the string to be resolved by VBA. Also, if
>>> SSN is text don't forget to wrap the test value in quotes by adding
>>> a pair of double quotes on either side of the test value:
>>>
>>> stLinkCriteria = "EntryNum=" & Me![EntryNum] & " AND SSN=""" &
>>> me.SSN & """"
>>>
>>> FWIW the brackets around fieldnames are not necessary unless the
>>> fieldname contains embedded spaces or special characters. Yours do
>>> not (congratulations!) and you can therefore omit the brackets.
>>>
>>>
>>> --
>>> Sandra Daigle [Microsoft Access MVP]
>>> Please post all replies to the newsgroup.
>>>
>>>
>>> Bonnie wrote:
>>>> Hello all! Using A02 on XP. Recently learned how to use
>>>> linking criteria. LUV IT! How can I make the one below
>>>> link to [EntryNum] AND [SSN]? Can't quite figure out the
>>>> quotes and such in the stLinkCriteria line. That's the
>>>> only place to make the changes, right?
>>>>
>>>> Private Sub Address_Click()
>>>> On Error GoTo Err_Address_Click
>>>>
>>>>     Dim stDocName As String
>>>>     Dim stLinkCriteria As String
>>>>
>>>>     stDocName = "DistribArchivedForm-Sub2-Addr"
>>>>
>>>>     stLinkCriteria = "[EntryNum]=" & Me![EntryNum]
>>>>     DoCmd.OpenForm stDocName, , , stLinkCriteria
>>>>
>>>> Exit_Address_Click:
>>>>     Exit Sub
>>>>
>>>> Err_Address_Click:
>>>>     MsgBox Err.Description
>>>>     Resume Exit_Address_Click
>>>>
>>>> End Sub
>>>>
>>>> Thanks in advance for any help or advice!
>>>
>>>
>>> .

Relevant Pages

  • Re: DLookUp Question
    ... >MsgBox "That SSN is already in the DataBase. ... The steps I use to get the nested quotes straight is to ... >>Bonnie wrote: ... >>If the ssn field is a numeric type field, ...
    (microsoft.public.access.formscoding)
  • Re: Need to use 2 fields in link criteria
    ... - First quote - VBA String Delimiter ... - Fourth quote - VBA String Delimiter ... > extra quotes.) ... >> SSN control is outside of the string to be resolved by VBA. ...
    (microsoft.public.access.formscoding)
  • Re: Need to use 2 fields in link criteria
    ... quotes before & me.SSN & and two sets after? ... (Yes, my SSN ... >control is outside of the string to be resolved by VBA. ... >> Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... I think it would be safe to say, use the Dim statement any time you are ... Dim basically tells vba that you want to initiate a new variable. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... and in the book; Access 2007 VBA Programmer's Reference. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ... when an SQL is processed (I use an SQL example because it is the most ...
    (microsoft.public.access.modulesdaovba)