Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13113

Re: Splitting a fields data into one or more new recordset(s)

Show all headers | View raw

If dtfDCArr were a string you'd want to Change
  & Replace(!dtfDCArr, "'", "''") & "', " ');"
To
  & Replace(!dtfDCArr, "'", "''") & "');"


But since it is a DateTime, you want to change
                    & Replace(!txfLoadNo, "'", "''") & "', '" _
                    & Replace(!dtfVenArr, "'", "''") & "', '" _
                    & Replace(!dtfDCArr, "'", "''") & "', " ');"
To
                    & Replace(!txfLoadNo, "'", "''") & "', #" _
                    & !dtfVenArr & "#, #" _
                    & !dtfDCArr & "#);"

The easy way to debug a dynamically created Sql statement is to put a 
breakpoint somewhere after the sql is created and before it gets 
executed.  Go to the Immediate window, and debug.print it.  Then copy 
the output to a query in Sql view, and see what Access thinks is bad.  
Fix and test again, if all OK make the change(s) in your code, and 
bada-da-bing, you are off to the races.

The replace() function doubles up any single quotes that might be in 
your data.  Imagine what the resulting string might look like if your 
data included the name O'Connor in it.  Without doubling up the single 
quote the Sql string would come out as 'O'Connor'.  Access WILL 
complain about an undelimited string.  What we want the Sql output to 
look like is 'O''Connor'.  Access will treat the double single quote as 
an Escaped single single quote, and Access will go with the flow.

I have been burned by Irish and Italian names many-many times in the 
past, so I wrap ALL string data used in my dynamicly created Sql 
statements with the Replace function to double up any embedded single 
quotes.

In this case your short DateTime fields need to surrounded by Access 
Date Delimiters (#), and there should never be any single quotes in 
your DateTime data, as that would be an invalid date which Access would 
hate.

Rdub

---
This email has been checked for viruses by AVG.
http://www.avg.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:11 -0700
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:50 -0700
  Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-06 06:32 -0700
  Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-06 10:19 -0400
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-07 05:38 -0700
    Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:20 -0400
      Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:41 -0400
        Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-08 05:26 -0700
          Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-08 10:04 -0400
            Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-08 10:57 -0700
              Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-10 05:55 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-10 06:58 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 05:54 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-11 10:57 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:49 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-14 05:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-18 05:35 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-10 09:58 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 06:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-11 06:40 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-11 15:26 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:51 -0700

csiph-web