How to enter a parameter in a Where clause?
I'm not all that familiar with Access/DAO, but you're concatenating the parameter value into your query. With ADO your command text would look something like this instead:
Const sql As String = "SELECT * FROM order_tbl WHERE invoice_no Is Null AND company_name = ? AND shiped = True"
The ? is a placeholder for a parameter value; instead of calling OpenRecordset(sql) directly, you need to make a Command and add a proper Parameter.
Seems DAO handles this a bit differently, see Parameterized queries in Access - surely one of the answers is applicable here. Because I don't like random ad-hoc queries hitting my databases I'd probably go with the QueryDefs approach in this answer:
Dim qdf As Querydef
Dim rst As Recordset
'then we'll open up the query:
Set qdf = CurrentDB.QueryDefs(qryname)
'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qryStartDate
qdf.Parameters(1) = qryEndDate
'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset
That would be:
Dim query As QueryDef
Set query = CurrentDB.QueryDefs("TheQuery")
query.Parameters(0) = Me.cmb_start_company.Column(1)
Dim result As Recordset
Set result = query.OpenRecordset
Query doesn't work after uploaded to Web Server
DUE_DATE is Date/Time type and you want to match all values for today's date ignoring the time of day. So ask for DUE_DATE greater than or equal to the earliest time of today (midnite) and less than tomorrow's date.
"SELECT * From COURSE1" & vbCrLf & _
"WHERE DUE_DATE >= Date() AND DUE_DATE < DateAdd('d', 1, Date())" & vbCrLf & _
"AND [CLASS] = '" & CCode & "' ORDER BY SUBJECT, LECT_NO, SUBTOPIC"
Also CLASS is a reserved word, so enclose that name in square brackets.
MS Access 2003 - Is there a way to programmatically define the data for a chart?
It is possible to change the dataset directly in vba as I have managed to do it. However the performance is not so good so I went back to filling the results to a temp table and basing the graph on that ( see my only asked stackoverflow question) however if the dataset is quite small then you can certainly make it work. I'm not in the office but if you want code I can post on Monday
EDIT: here is the old code module I used. This is the full thing but the key part you are going to be looking at is the part about opening the datasheet of the graph and then changing the value of it like this .cells(1,0)="badger".
I enevtly dumped this method and went with a temp table as in my app the graph is redraw quite a lot and I needed to go for the fastest possible method to give a "real time" feel to it but it might be just fine for your needs
Public Sub Draw_graph(strGraph_type As String)
Dim objGraph As Object
Dim objDS As Object
Dim i As Byte
On Error GoTo Error_trap
Dim lRT_actual As Long
Dim lRT_forecast As Long
Dim Start_time As Long
Dim aCell_buffer(49, 4) As Variant
Me.acxProgress_bar.Visible = True
Me.acxProgress_bar.Value = 0
Set objGraph = Me.oleCall_graph.Object
Set objDS = objGraph.Application.datasheet
Start_time = GetTime()
With objDS
.cells.Clear
Select Case strGraph_type
Case Is = "Agents"
'**************************
'** Draw the agent graph **
'**************************
.cells(1, 1) = "Start Time"
.cells(1, 2) = "Provided"
.cells(1, 3) = "Required"
.cells(1, 4) = "Actual Required"
For i = 1 To 48
.cells(i + 1, 1) = Format(DateAdd("n", (i - 1) * 15, "08:00:00"), "HHMM")
If Me.Controls("txtAgents_pro_" & i) > 0 Then
.cells(i + 1, 2) = Me.Controls("txtAgents_pro_" & i) + Me.Controls("txtAgents_add_" & i)
Else
.cells(i + 1, 2) = 0
End If
If Me.Controls("txtAgents_req_" & i) > 0 Then
.cells(i + 1, 3) = Me.Controls("txtAgents_req_" & i)
End If
If Me.Controls("txtActual_" & i) > 0 Then
.cells(i + 1, 4) = Erlang_Agents(Me.txtServiceLevel, Me.txtServiceTime, Me.Controls("txtActual_" & i) * 4, Me.txtAVHT + CLng(Nz(Me.txtDaily_AVHT_DV, 0)))
End If
'update the progress bar
If Me.acxProgress_bar.Value + 2 < 100 Then
Me.acxProgress_bar.Value = Me.acxProgress_bar.Value + 2
Else
Me.acxProgress_bar.Value = 90
End If
Next i
Case Is = "Calls"
'**************************
'** Draw the Calls graph **
'**************************
.cells(1, 1) = "Start Time"
.cells(1, 2) = "Forecast"
.cells(1, 3) = "Actual"
For i = 1 To 48
.cells(i + 1, 1) = Format(DateAdd("n", (i - 1) * 15, "08:00:00"), "HHMM")
If Me.Controls("txtForecast_" & i) > 0 Then
.cells(i + 1, 2) = Me.Controls("txtForecast_" & i)
Else
.cells(i + 1, 2) = 0
End If
If Me.Controls("txtActual_" & i) > 0 Then
.cells(i + 1, 3) = Me.Controls("txtActual_" & i)
End If
If Me.acxProgress_bar.Value + 2 < 100 Then
Me.acxProgress_bar.Value = Me.acxProgress_bar.Value + 2
Else
Me.acxProgress_bar.Value = 90
End If
Next i
Case Is = "Call Deviation"
'**************************
'** Draw the Call Deviation graph **
'**************************
.cells(1, 1) = "Start Time"
.cells(1, 2) = "Deviation"
lRT_actual = 0
lRT_forecast = 0
For i = 1 To 48
lRT_actual = lRT_actual + Me.Controls("txtActual_" & i)
lRT_forecast = lRT_forecast + Me.Controls("txtForecast_" & i)
.cells(i + 1, 1) = Format(DateAdd("n", (i - 1) * 15, "08:00:00"), "HHMM")
.cells(i + 1, 2) = lRT_actual - lRT_forecast
If Me.acxProgress_bar.Value + 2 < 100 Then
Me.acxProgress_bar.Value = Me.acxProgress_bar.Value + 2
Else
Me.acxProgress_bar.Value = 90
End If
Next i
Case Is = "Call Deviation %"
'**************************
'** Draw the Call Deviation % graph **
'**************************
.cells(1, 1) = "Start Time"
.cells(1, 2) = "Deviation"
lRT_actual = 0
lRT_forecast = 0
For i = 1 To 48
lRT_actual = lRT_actual + Me.Controls("txtActual_" & i)
lRT_forecast = lRT_forecast + Me.Controls("txtForecast_" & i)
.cells(i + 1, 1) = Format(DateAdd("n", (i - 1) * 15, "08:00:00"), "HHMM")
If lRT_forecast > 0 Then
.cells(i + 1, 2) = (lRT_actual - lRT_forecast) / lRT_forecast
End If
If Me.acxProgress_bar.Value + 2 < 100 Then
Me.acxProgress_bar.Value = Me.acxProgress_bar.Value + 2
Else
Me.acxProgress_bar.Value = 90
End If
Next i
Case Is = "SLA"
'**************************
'*** Draw the SLA graph ***
'**************************
.cells(1, 1) = "Start Time"
.cells(1, 2) = "SLA"
.cells(1, 3) = "Actual SLA"
For i = 1 To 48
.cells(i + 1, 1) = Format(DateAdd("n", (i - 1) * 15, "08:00:00"), "HHMM")
If Me.Controls("txtSLA_" & i) > 0 Then
.cells(i + 1, 2) = Me.Controls("txtSLA_" & i) / 100
Else
.cells(i + 1, 2) = 0
End If
If Me.Controls("txtActual_SLA_" & i) > 0 Then
.cells(i + 1, 3) = Me.Controls("txtActual_SLA_" & i)
End If
If Me.acxProgress_bar.Value + 2 < 100 Then
Me.acxProgress_bar.Value = Me.acxProgress_bar.Value + 2
Else
Me.acxProgress_bar.Value = 90
End If
Next i
End Select
End With
Set objDS = Nothing
Set objGraph = Nothing
Me.acxProgress_bar.Visible = False
Exit Sub
Error_trap:
DoCmd.Hourglass False
MsgBox "An error happened in sub Draw_graph, error description, " & Err.Description, vbCritical, "Tracker 3"
End Sub
Escape input data in SQL queries when using ODBC + Access
I've been trying out random stuff. It seems that odbc_prepare() detects parameters if you use one of these syntaxes (or you even mix them):
INSERT INTO foo (bar) VALUES (:param)INSERT INTO foo (bar) VALUES ([param])
However, odbc_execute() will complain about missing parameters no matter what you feed it with (numeric array, associative array...). And it'll know the exact number of parameters that cannot be found. That makes the whole mechanism completely pointless.
Sad to say, my best solution so far is this:
/**
* Escape a string to be inserted into Access via ODBC
*/
function odbc_escape_string_access($value){
$replacements= array(
"'" => "''",
);
return strtr($value, $replacements);
}
It's horrible but I couldn't find anything better.
dynamically generating an SQL statement based on user form input
You need to use query parameters or else you are vulnerable to SQL injection attacks. Never trust the client. Any disgruntled employee could cause serious damage to your data.
See: How do you create a parameterized query in MS Access 2003 and use other queries/forms to fill the parameters and obtain a resultset
Is it possible to create a recursive query in Access?
No, It isn't. Recursive queries are supported in SQL Server after SServer 2005, but not in Access.
If you know the number of levels beforehand, you could write a query, but it wouldn't be a recursive one.
In SQL Server, CTE (An SQL extension) is used for that : see http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx
Regular SQL however does not have Recursivity support.
Access VBA, unescaped single quotes, Replace(), and null
If you want to keep everything inline, you can use an immediate If function (IIf):
IIf(IsNull(rstFrom("Student").Value), " Is Null", "= " & Replace(rstFrom("Student").Value)
That will be a nightmare to read and maintain, though. You are better off writing your own function to handle the change in comparison operator as well as the apostrophe escaping:
Function CompFld(Val As Variant) As String
If IsNull(Val) Then
CompFld = " Is Null "
Else
CompFld = "= '" & Replace(Val, "'", "''") & "' "
End If
End Function
Use it as so:
Dim SQL As String
SQL = "SELECT * FROM Student " & _
"WHERE (StudentName " & CompFld(rstFrom("Student").Value) & " AND " & _
" School " & CompFld(rstFrom("School").Value) & ") " & _
" OR (SSN " & CompFld(rstFrom("Social").Value) & ") "
Set rstDuplicate = CurrentDb.OpenRecordset(SQL)
If rstDuplicate.RecordCount = 0 Then
'Duplicate was not found
rstTo.AddNew
' Add fields to the new table
rstTo.Update
End If
Related Topics
Re-Writing "Fuzzy Join" Functions from R to SQL
How to Generate Date Series to Occupy Absent Dates in Google Biqquery
Postgresql Selecting Most Recent Entry for a Given Id
Is Activerecord's "Order" Method Vulnerable to SQL Injection
Fetch Records That Are Non Zero After the Decimal Point in Postgresql
What Did Mongodb Not Being Acid Compliant Before V4 Really Mean
Add Primary Key to Existing Table
Rodbc Queries Returning Zero Rows
Retrieve Oracle Last Inserted Identity
Role of Selectivity in Index Scan/Seek
Extract Numbers from a Text in SQL Server