function call in query vs VBA

Topic

New Reply

I am turning a bunch of spreadsheets into a useful database. My problem is turning text that represents “Yes” into a Yes/No field.
I have written a function that returns true if the Variant I give it is something like Y, y, etc.
I have a query that works just fine using this function.

Now I want to convert it to SQL in code, and it doesn’t work as expected. The code fragment is as follows:

  strSQL = "INSERT INTO tblCodeTypeTagList ( TrendPoint ) SELECT "
  strSQL = strSQL & YesToBool("[" & strtblCodeType & "].CiTectTrend") & " AS Expr1 FROM ["
  strSQL = strSQL & strtblCodeType & "];"
  myDB.Execute strSQL

This works in the query, but in the code the result of the function is calculated only once. I can see that the function is using the table.field as a string and evaluating that, rather than the data stored in table.field.
Any suggestions how to rewrite the function or otherwise achieve the end result?

Thanks,
Marty

Reply | Quote

Replies

If this is supposed to pass a table name and field name into the function as a string, then it’s doing exactly what you told it to.

YesToBool(“[” & strtblCodeType & “].CiTectTrend”)

Now, can you explain what you actually want it to do? If you want to include the function in the SQl, do it like this:

strSQL = strSQL & ” YesToBool([” & strtblCodeType & “].CiTectTrend) AS Expr1 FROM [“

Reply | Quote

Thanks Charlotte.

I hadn’t realised I could build the function into the SQL rather than call it as I made the SQL clause. Simple really.

Works just luvverly.

Reply | Quote