[AccessD] Need some Simple SQL help

Thanks Stuart - very useful!
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 19. marts 2010 00:58
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Need some Simple SQL help

I just used a couple of visible characters to represent Ascii 13 and Ascii 10 for clarity.
I could have just as easily used other characters such as "#" and "^".

In essence,  I just did this:

Function ShowReturns(strText)
ShowReturns = Replace(Replace(strText, Chr$(13), "~"), Chr$(10), "!")
End Function

? Showreturns(strSQL)

-- 
Stuart

On 19 Mar 2010 at 0:31, Asger Blond wrote:

> Hi Stuart,
> Interesting observation. And curious: how did you get the resolution "~!" for vbCrLf?
> I often need this kind of information, but neither a MsgBox or a Debug.Print will do. So what statement did you use to get what vbCrLf is resolved to?
> TIA
> Asger
> 
> 
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
> Sendt: 17. marts 2010 23:00
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Need some Simple SQL help
> 
> The vbCRLFs would definitely have been causing a problem.
> 
> Substituting "~!"  for vbCrLf for visibilty: 
> 
> ..., Customer.[Last Name], " & vbCrLf  & "Customer.Address1,...
> 
> resolves to:  
> ...,Customer.[Last Name],~!Customer.Address1,....
> which should cause an error when you Access tries to resolve it as a recordsource.
> 
> If you have correctly removed the vbCRLF part, you should have:
> ..., Customer.[Last Name], " &  "Customer.Address1,...
> 
> which resolves to:
>  ...,Cutomer.[Last Name],Customer.Address1,....
> 
> 
> Another possible problem - is CustomerID numeric? If not, you need to enclose ICID in 
> quotes in the SQL string.
> 
> 
> -- 
> Stuart
> 
> 
> On 17 Mar 2010 at 17:23, Robert wrote:
> 
> > Ok,
> >   Removed the vbCrLf's, but as suspected made no difference.. So strange....
> > 
> > WBR
> > Robert
> > 
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> > Sent: Wednesday, March 17, 2010 8:32 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Need some Simple SQL help
> > 
> > Don't see the purpose of the vbCrLf's in a SQL - maybe removing them will do?
> > 
> > Asger
> > 
> > -----Oprindelig meddelelse-----
> > Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Robert
> > Sendt: 17. marts 2010 12:57
> > Til: 'Access Developers discussion and problem solving'
> > Emne: [AccessD] Need some Simple SQL help
> > 
> > Goal: Try to speed up the filter of a form with 2 subforms
> > 
> > I'm trying to move to a specific record on the main form (and another
> > un-linked subform) from a record on a subform. The main form is based on a
> > saved query, and has it's "Data Entry" property set as YES.
> > 
> > I can use this and it works:
> > 
> >     .Filter = "[Customer ID]=" & lCID
> >     .FilterOn = True
> > 
> > But If I try to use the SQL below and RecordSource it fails.
> > 
> > strSQL = "SELECT Customer.[Customer ID], Customer.[First Name],
> > Customer.[Mid Init], Customer.[Last Name], " & vbCrLf & _
> >                 "Customer.Address1, Customer.Address2, Customer.City,
> > Customer.Zip, Customer.HomePhone, " & vbCrLf & _
> >                 "Customer.WorkPhone1, Customer.Ext1, Customer.WorkPhone2,
> > Customer.Ext2, Customer.CellPhone, " & vbCrLf & _
> >                 "Customer.Ext3, Customer.Phone_Other, Customer.[Customer
> > Type], Customer.Comment, Customer.Balance, " & vbCrLf & _
> >                 "Customer.[Type of Terms], Customer.[Term Day],
> > Customer.[Contact person], Customer.[Tax Exempt Status], " & vbCrLf & _
> >                 "Customer.[Tax Number], Customer.[Ship To], Customer.[Uses
> > PO], Customer.Active, Customer.State, " & vbCrLf & _
> >                 "Customer.Advertizements, Customer.SpecNeeds,
> > Customer.LocationType, Customer.[Service ID], Customer.MDRID, " & vbCrLf & _
> >                 "Customer.TentPerm, Customer.Landlord, Customer.Directions,
> > Customer.ExtStaticNotes, Customer.IntNotes, " & vbCrLf & _
> >                 "Customer.Field1, Customer.Field4, Customer.Ext,
> > Customer.CreatedBy, Customer.CreateDate, Customer.Phone1Type, " & vbCrLf & _
> >                 "Customer.Phone2Type, Customer.Phone3Type,
> > Customer.Phone4Type " & vbCrLf & _
> >         "FROM Customer " & vbCrLf & _
> >         "WHERE Customer.[Customer ID] =" & lCID
> >         .RecordSource = strSQL
> > 
> > Can you see what I'm doing wrong, and is this faster?
> > 
> > WBR
> > Robert
> > 
> > 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> > 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> > 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com