Select count data in access with textbox input value condition

[Ngoclong226]

Hi
I have a database access with “LAL data” table and “Job_no” field.
I have create a userform in excel with input textbox “txt_jobno”
How can i count the number of fields in access table with criteria as textbox input value.
My code of selecting the data in acess:
Const strSQL_c as string = “select count([LAL data].Job_no) as CountofJob_no From [LAL data] Where ([LAL data].Job_no = “”me.txt_jobno.value””)”
But it did not work.
Could anyone help?

 

[Joe4]

See if this works better for you:
Is me.txt_jobno.value a variable's value?
If so, I don't think you want quotes around it, i.e.

[COLOR=#333333]Const strSQL_c as string = "select count([LAL data].Job_no) as CountofJob_no From [LAL data] Where ([LAL data].Job_no = me.txt_jobno.value)"[/COLOR]

 

[Ngoclong226]

Thanks for you help.
I try your solution but it happen error “ no value given for one or more required parameter”. Here is all the code:
Const strSQL_c As String = "SELECT Count([LAL data].Job_No) AS CountOfJob_No FROM [LAL data] WHERE ([LAL data].Job_No = me.Txt_JobNo.Value)"

Const strConnection_c As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\70F4908\Desktop\Programming document\CODE\Production data input project\Data.accdb;Mode=Read;Persist Security Info=False"

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim lngCount As Long

Set cn = New ADODB.Connection

cn.Open strConnection_c

Set rs = New ADODB.Recordset

rs.Open strSQL_c, cn, adOpenForwardOnly ‘(this line happen eror)

If Not rs.EOF Then

lngCount = rs.Fields(0).Value

End If

 

[Joe4]

I try your solution but it happen error “ no value given for one or more required parameter”.

I believe that is telling you that it is not receiving any value from "me.Txt_JobNo.Value".

Where exactly have you placed this VBA code?
How is it being called/run?
Are you sure that you have the variable named correctly?

An easy way to check to see if you can get the value in that manner within that code is to place this line at the top of your code, and see what it returns:
MsgBox me.Txt_JobNo.Value

 

[Ngoclong226]

Hi
I put the code in a command button in a simple input form (1 textbox, 1 button)
When i input a value in textbox then click button, i will return the how many jobno in the database match with the input data.
If i only put the code msgbox txt_jobno.value it still show the correct value i input to the textbox.