queries based on user input

[googleman]

Hi Im really new to access and was wondering if you could help me.

I want users to say enter any date and the query will pull back all the relevant info for that date.

I also want to create a query where users can enter a box number and get a record of the tapes within that box.

how can i achieve this please?I appreciate your help, thanks

table name: Tapes
fields :Tape_Date
Box_Serial_Number
Tape_Name

 

[dyarwood]

I have done something similar using VBA code with SQL string being executed and the output being inputted into a table.

Function Filter()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim Fie() As String
Dim Fies As String
Dim de() As String
Dim des As String
DoCmd.DeleteObject acTable, "Summary"


c = 6
i = 0
Do Until c = 7
i = i + 1
Fies = InputBox(Prompt:="Enter the field heading you want to filter by&quot
FIESS = UCase(Fies)
des = InputBox(Prompt:="Enter the criteria to filter for&quot
DDES = UCase(des)
ReDim Preserve Fie(0 To i)
ReDim Preserve de(0 To i)

Fie(i) = FIESS
de(i) = DDES

c = MsgBox(Prompt:="Do you want to add anymore criteria?", Buttons:=vbYesNo)
Loop
l = 1

Do Until l > i
If l = i Then
c = ""
Else
c = " AND "
End If

cri = cri & "(DataTable." & Fie(l) & &quot" & " = '" & de(l) & "'" & c
l = l + 1

Loop

strSQL = "SELECT * INTO Summary"
strSQL = strSQL & vbCrLf & "FROM DataTable"
strSQL = strSQL & vbCrLf & "WHERE (" & cri & &quot"

cnn.Execute strSQL

End Function

This is used to let the user decide which field heading they want to filter by then what they want to filter for and then if they want extra filters will let that to. It then finishes off by inputted the filtered DATATABLE data into the SUMMARY table.

Let me know if this helps

dyarwood