[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
>