How to convert Access VBA with string queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to convert Access VBA with string queries

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-12-05 : 06:07:49

How to convert Access VBA with string queries into Stored Procedure
Access 2003 and SQL 2000 Server

I have a code below that work with SQL 2000 Server, and I would like to convert it to Stored procedure.
Currently the procedure below resided in the Access 2003 front-end. It just needs to be in the
SQL 2000 Server.

There are 6 combo boxes in the Form Selector that are used in the code below

Forms!Selector!From_Date
Forms!Selector!To_Date
Forms!Selector!Dept
Forms!Selector!so
Forms!Selector!Item
Forms!Selector!Sectionno

/////////////////////////////////////////////////////////

Private Sub get_subfrm_recs()
On Error GoTo Err_get_subfrm_recs
Dim strsql As String
Dim strDate As String
Dim strOrd As String
Dim strfrmrs As String
Dim dtstart As String
Dim dtend As String
Dim strWhere As String

dtstart = "'" & Format(Forms!Selector!From_Date, "yyyymmdd") & "'"
dtend = "'" & Format(Forms!Selector!To_Date, "yyyymmdd") & "'"

strsql = "SELECT DISTINCT [1_Job - Parent].SONumber, [1_Job - Parent].Department_Name,"
strsql = strsql & " [1_Job - Parent].ItemNumber, [1_Job - Parent].SectNumber,"
strsql = strsql & " [1_Job - Parent].RecordInitiateDate, [1_Job - Parent].MechUser,"
strsql = strsql & " [1_Job - Parent].ElecUser, [1_Job - Parent].GreenTagUser,"
strsql = strsql & " [1_Job - Parent].GreenTagDate,"
strsql = strsql & " Ref_DepartmentID.ID"
strsql = strsql & " FROM Ref_DepartmentID RIGHT JOIN [1_Job - Parent]"
strsql = strsql & "ON Ref_DepartmentID.ID = [1_Job - Parent].DepartmentID"
strsql = strsql & " WHERE"

If Not IsNull(Forms!Selector!From_Date) And Not IsNull(Forms!Selector!To_Date) Then
strDate = strDate & " [1_Job - Parent].RecordInitiateDate"
strDate = strDate & " Between " & dtstart
strDate = strDate & " And " & dtend & " And "
End If

If Len(Forms!Selector!Dept) <> 0 Then
strWhere = " Ref_DepartmentID.ID = " & Forms!Selector!Dept
End If

If Len(Forms!Selector!so) <> 0 Then
strWhere = strWhere & " AND [1_Job - Parent].SONumber = " & Forms!Selector!so
End If

If Len(Forms!Selector!Item) <> 0 Then
strWhere = strWhere & " AND [1_Job - Parent].ItemNumber = '" & Forms!Selector!Item & "'"
End If

If Len(Forms!Selector!Sectionno) <> 0 Then
strWhere = strWhere & " AND [1_Job - Parent].SectNumber = '" & Forms!Selector!Sectionno & "'"
End If

strOrd = " ORDER BY [1_Job - Parent].RecordInitiateDate DESC"
strfrmrs = strsql & strDate & strWhere & strOrd

Debug.Print strsql & strDate & strWhere & strOrd

Me.Q_FilteringQuery_subform.Form.RecordSource = strfrmrs

If Me.Q_FilteringQuery_subform.Form.RecordsetClone.RecordCount = 0 Then
Me.Q_FilteringQuery_subform.Visible = False
Else
Me.Q_FilteringQuery_subform.Visible = True
End If

Exit Sub

Err_get_subfrm_recs:
MsgBox "Error: " & Err & " " & Err.Description
End Sub

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 15:05:08

You'll need to change the data types of the parameters to match the columns in your table, but this should do it

CREATE PROC YourProcName
(
@FromDate datetime = NULL, @ToDate datetime = NULL,
@Dept int = NULL,
@SONumber int = NULL,
@Item varchar(20) = NULL,
@Sect varchar(20) = NULL
)
AS
SELECT DISTINCT [1_Job - Parent].SONumber, [1_Job - Parent].Department_Name,
	[1_Job - Parent].ItemNumber, [1_Job - Parent].SectNumber,
	[1_Job - Parent].RecordInitiateDate, [1_Job - Parent].MechUser,
	[1_Job - Parent].ElecUser, [1_Job - Parent].GreenTagUser,
	[1_Job - Parent].GreenTagDate,
	Ref_DepartmentID.[ID]
FROM Ref_DepartmentID RIGHT JOIN [1_Job - Parent]
ON Ref_DepartmentID.[ID] = [1_Job - Parent].DepartmentID
WHERE ([1_Job - Parent].RecordInitiateDate BETWEEN @FromDate AND @ToDate OR (@FromDate IS NULL OR @ToDate IS NULL))
	AND (Ref_DepartmentID.[ID] = @Dept OR @Dept IS NULL)
	AND ([1_Job - Parent].SONumber = @SONumber OR @SONumber IS NULL)
	AND ([1_Job - Parent].ItemNumber = @Item OR @Item IS NULL)
	AND ([1_Job - Parent].SectNumber = @Sect OR @Sect IS NULL)
ORDER BY [1_Job - Parent].RecordInitiateDate DESC

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-12-05 : 23:07:40

Thank you very much snSQL for your help. I really appreciate the time that you took
to convert that for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources