Search results for query: querydefs

The difficult and error-prone problem of cobbling together SQL statements in code is why DAO allows you to create and run a temporary QueryDef. Here's a code example... Private Const SQL_INSERT_SCORE As String = _ "INSERT INTO tblTestScores " & _ "( dtTestDate, numScore...

Here's code that works using the change event and a parameterized QueryDef... Private Const SQL_SELECT As String = _ "SELECT ID, Oddelek " & _ "FROM Oddelki " & _ "WHERE Oddelek LIKE p0 " & _ "ORDER BY Oddelek;" Private qdf_ As DAO.QueryDef Property Get qdfSearch() As...

More reliable still is a temporary QueryDef, which handles all your delimiters and date formats for you... Const SQL As String = _ "SELECT ti.* " & _ "FROM dbo_DCEREPORT_INTERRUPTOPERATIONS As ti INNER JOIN " & _ "dbo_DCEREPORT_MACHINES As tm ON...

Yes. This is why you no longer need to worry about delimiters. The QueryDef already knows the data type of all the fields.

By far the most reliable way to run SQL like this is to use a temporary parameterized QueryDef. Consider code like... Private Sub cmdComment_Click() Const SQL As String = _ "UPDATE Leave As t SET t.Comments = p0 " & _ "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"...

QueryDef will work 100% of the time without any modifications to your data and without specifying any delimiters. It is the 'proper' way, it is the simpler way, and it is the most readable way. It solves string delimiter confusion, date delimiter and format confusion like magic. You will...

Consider refactoring code as follows... Private Sub Command23_Click() ' user interface code If PerformDeleteQuery Then Me.lst_From_Date.Requery Me.lst_To_Date.Requery End If End Sub Private Function Validate() As Boolean ' validation code If...

Or, use a QueryDef.... In this example, if the txtString control contains 5'3", no extra effort is required. It just works. Same with dates. You never have to Format(), delimit, convert to string. The QueryDef handles it, because it knows the types of all the fields already. Sub Test()...

The best way to deal with delimiters in your data like that is to use a parameter query and a temp QueryDef, like... Function GetRSTMcHammer() As DAO.Recordset Const SQL As String = _ "SELECT * FROM YourTable WHERE MaterialID = p0 " With CurrentDb.CreateQueryDef("", SQL)...

Or, don't worry about formats and delimiters and use a parameterized QueryDef... Function GetMaxCost(ProductID As Long, Supplier As String, Date_Quoted As Date) Const SQL As String = _ "SELECT CostPrice " & _ "FROM qMaxPricePartSupplier " & _ "WHERE ProductID = p0 " &...

Faster way--like way faster--inside a transaction with a QueryDef... Const SQL As String = _ "INSERT INTO Table1(data) VALUES( p0 )" Dim i As Integer DBEngine.BeginTrans With CurrentDb.CreateQueryDef("", SQL) For i = 1 To 1724 .Parameters(0)...

Use a parameterized QueryDef. Cheers, Mark

The other way is to use a parameterized QueryDef, which handles delimiters automactically. Consider code like... Sub Test19236412() Const SQL As String = _ "UPDATE tblLandParcels As t " & _ "SET t.Hazards = p0 " & _ "WHERE (t.Hazards Is Null Or t.Hazards = '') " & _...

Welcome to the forum!!! You can just use a parameterized QueryDef to open the recordset, like... Const SQL_SELECT As String = _ "SELECT * " & _ "FROM tblCustomerMaster " & _ "WHERE Customer = p0 " Private Sub cboCustomerSearch_AfterUpdate() With CurrentDb.CreateQueryDef(""...

I commonly recommend you use a QueryDef, and this is no exception. It's more verbose in code terms, but simpler to understand the SQL, easier to see where each parameter value is being assigned, and delimiters are not your problem. Consider code like... Public Sub logging(Activity As String...

...is a member of Access.Application. To execute a query in a DAO.Database, you can use the execute method, but the DAO.Database also has a QueryDefs collection, so you could run a named query like... db.QueryDefs("qryMkTbl").Execute ...using the execute method (or OpenRecordset) of the...

Use a Parameterized QueryDef instead. What I would always do in this case is use a parameterized QueryDef so that embedded delimiters do not break my SQL, and my users are thereby able to store any characters they want without these kind of on-the-fly text editing solutions. Let's say a user...

...the parameter, and execute the query, and you are done. No mucking around with delimiters or formats. Consider... with currentdb.querydefs("YourParamQuery") .parameters(0) = Me.tbValidDateField 'no delimiters or formats required .execute .close end with ...or if...

Mark_, I guess that could be. Kirkm has not provided sample data for what dat(0) might hold. I assumed it was a string with embedded delimiters, like... 8' x 2"x4" ...which is impossible to delimit without altering the data, or using a parameterized QueryDef. If it is what you are saying...

If you want to do an update that accepts null and dates, the simplest way is to use a QueryDef, like... Private Sub Test192346184() Const SQL_UPDATE As String = _ "UPDATE tYourTable " & _ "SET HireDate = p0 " & _ "WHERE UID = p1 " With...