Re: Is date the problem or something else
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 09/23/04
- Next message: William \(Bill\) Vaughn: "Re: Update Database from DataAdapter"
- Previous message: Andy-TPCETC: "Is date the problem or something else"
- In reply to: Andy-TPCETC: "Is date the problem or something else"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Sep 2004 16:03:59 -0700
I guess performance is not an issue. This will take quite awhile just to
concatenate the strings!
I suggest building a Command object with a few parameters and try to reduce
the number of concatenations to a minimum. In VB.NET we can use the
stringbuilder class to do this. The Command object will properly frame the
dates and loose single quotes as well.
To debug this, dump the SQL just before you execute it and look at what got
built. If it's SQL Server you can use the Profiler to do the same.
hth
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Andy-TPCETC" <AndyTPCETC@discussions.microsoft.com> wrote in message news:927469CB-7E01-4989-97F0-71FC97EB405E@microsoft.com... > The following inherited SQL is erroring out with a '3075-syntax error in > date > in query experssion' Any help would be greatly appreciated. > > > Dim SQL As String > > SQL = "SELECT DISTINCT [_program_enrollment].OFFICE_ID, > [_program_enrollment].program_enrollment_id, [_agent_unique].a_name, > [_program_enrollment].program_type, " > SQL = SQL & "[_program_enrollment].ENROLLMENT_COMPLETION_DATE, > [_agent_unique].LAST_NAME AS a_last, " > SQL = SQL & "[_agent_unique].FIRST_NAME AS a_first, > [_seeker_data].LAST_NAME, [_program_enrollment].exit_outcome, " > SQL = SQL & "[_seeker_data].FIRST_NAME, [_seeker_data].MIDDLE_INITIAL, > [_seeker_data].SSN, " > SQL = SQL & "[_seeker_data].address1, [_seeker_data].address2, > [_seeker_data].city, [_seeker_data].state, [_seeker_data].zip, " > SQL = SQL & "[_seeker_data].mailing_address1, > [_seeker_data].mailing_address2, [_seeker_data].mailing_city, > [_seeker_data].mailing_state, [_seeker_data].mailing_zip, " > SQL = SQL & "[_seeker_data].PRIMARY_PHONE, [_seeker_data].SECONDARY_PHONE, > [_seeker_data].selective_service_number, " > SQL = SQL & "[_seeker_data].emergency_contact_name, > [_seeker_data].emergency_contact_phone, [_program_enrollment].age, " > SQL = SQL & "[_seeker_data].EMAIL, [_seeker_data].gender, > [_seeker_data].date_of_birth, [_seeker_data].SEEKER_ID " > SQL = SQL & "FROM _seeker_data INNER JOIN ((htm_ofc_list INNER JOIN > _program_enrollment " > SQL = SQL & "ON [htm_ofc_list].OFFICE_ID = > [_program_enrollment].OFFICE_ID) > INNER JOIN " > SQL = SQL & "_agent_unique ON [_program_enrollment].AGENT_ID = > [_agent_unique].agent_id) " > SQL = SQL & "ON [_seeker_data].SEEKER_ID = [_program_enrollment].SEEKER_ID > " > SQL = SQL & "WHERE [_program_enrollment].OFFICE_ID = '" & Office & "' " > SQL = SQL & "And [_program_enrollment].exit_outcome <>'OTHER_INST' " > SQL = SQL & "And [_program_enrollment].exit_outcome <>'OTHER_HEALTH' " > SQL = SQL & "And [_program_enrollment].exit_outcome <>'OTHER_DECEASED' " > SQL = SQL & "And [_program_enrollment].exit_outcome Not Like '*RESERVIST*' > " > SQL = SQL & "And [_program_enrollment].ENROLLMENT_COMPLETION_DATE >= #" & > Forms!Main_prep!txtRange_start & "# " > SQL = SQL & "And [_program_enrollment].ENROLLMENT_COMPLETION_DATE <= #" & > Forms!Main_prep!txtRange_end & "# " > SQL = SQL & "And [_program_enrollment].program_type like 'WIA*' " > SQL = SQL & "AND [_program_enrollment].program_type not like 'WIA NEG*' " > SQL = SQL & "AND [_program_enrollment].program_type not like 'WIA RAPID*' > " > SQL = SQL & "AND [_program_enrollment].program_type not like 'WIA 10%*' " > SQL = SQL & "ORDER BY [_agent_unique].a_name, > [_program_enrollment].ENROLLMENT_COMPLETION_DATE" > > Set RRs = db.OpenRecordset(SQL, dbOpenDynaset) > > Runtime Error 3075 > Syntax error in date expression > > -- > Thanks Andy
- Next message: William \(Bill\) Vaughn: "Re: Update Database from DataAdapter"
- Previous message: Andy-TPCETC: "Is date the problem or something else"
- In reply to: Andy-TPCETC: "Is date the problem or something else"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
