SQL -> VBA Translation

Jul 19, 2006

I'm having trouble converting SQL to VBA. Can someone help?

Original SQL
SELECT tsubPermissionList.UserID, tblMainData.TrackingID, tblMainData.WorkOrder, tblMainData.ActionDescription, tblMainData.Facility, tblMainData.Status, tblMainData.ResponsibleParty, tblMainData.[WHS ID], tblMainData.[PM ID], tblMainData.ProgramID, tblMainData.Location, tblMainData.Branch, tblMainData.Unit, tblMainData.DueDate, tblMainData.ActualStartDate, tblMainData.ActualCompletionDate, tblMainData.Completed, tblMainData.LastModified, tblMainData.CreatedBy, tblMainData.CreatedWhen, tblMainData.EditedBy, tblMainData.EditedWhen, tblMainData.Comments, tblMainData.EmailSent, tblMainData.EmailSender, tblMainData.Selected, *

FROM tsubPermissionList INNER JOIN tblMainData ON tsubPermissionList.FullName = tblMainData.ResponsibleParty

WHERE (((tblMainData.WorkOrder) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*")) OR (((tblMainData.ActionDescription) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*")) OR (((tblMainData.Facility) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*")) OR (((tblMainData.Status) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"));


My attempt to translate it to VBA.
strSelect = "SELECT DISTINCTROW tblMainData.TrackingID, tblMainData.WorkOrder, tblMainData.ActionDescription, tblMainData.Facility, tblMainData.ResponsibleParty, tblMainData.Status, tsubPermissionList.UserID, tsubPermissionList.FullName, *"
strFrom = " FROM tsubPermissionList"
strJoin = " INNER JOIN tblMainData ON tsubPermissionList.FullName = tblMainData.ResponsibleParty"
strWhere = " WHERE tblMainData.WorkOrder Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
" OR tblMainData.ActionDescription Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
" OR tblMainData.Facility Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
" OR tblMainData.Status Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" Thanks,
PC

View Replies


ADVERTISEMENT

Checkboxes True/False -1/0 ... Lost In Translation

Mar 15, 2006

Hi all,

My client has an Access database where the value of a checkbox is stored in a field called Exclude_PropCount as -1 or 0. I am trying to get this form working online, but have been unable to successfully store this value in the db. I have set up a simple page to test it, and my relevant code is this:

sql_save = "INSERT INTO HVM_Data (VC, Exclude_PropCount) VALUES ('a9', '-1')"

con.Execute sql_save

set duplicateVC1 = db.Execute("SELECT Exclude_PropCount FROM HVM_Data WHERE VC = 'a9'")

response.write duplicateVC1("Exclude_PropCount")

But no matter whether I have '-1' or '0' in the INSERT statement, the response.write line always returns "False". And of course I've tried inserting "true" and "false", but it only accepts an integer.

As exceptionally bad timing would have it, my server is slightly fubar and I can't download the database to my PC to see what's actually being stored there, but looking at the above code, do you see anything I'm doing inherently *wrong*? This is my first time with checkboxes so I won't be surprised if that's the case. Hints?

Thanks!

View 1 Replies View Related

Automatic Translation Of A Column (field) Currency

Jan 18, 2015

Currently, I develop an Access application (+ VBA).

Clients computers are English and the developers works with french clients.

When updating the application to the French posts, the word "currency" is automatically replaced by "devise" in all requests.

The name of the column in the table remains "currency".

(US / UK) => devise (fr)

How to disable the automatic "translation"?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved