cbo Query Question

[Bryan Hughes]

Hello,

I have a problem with a combo box that is baffling me.

I have a new client entry form that checks for matching criteria before
continuing. If matching criteria is found, user is informed and can open
another form to view matching results.

In the matching results form openargs are passed from first form and
separated and converted into appropriate data types.

In On_Load event, evaluates criteria and assigns a value, depending on the
criteria match (Full Name, Last Name, DOB, Like Last Name).

Part of the On_Load event is :
****************************************************
strOpenArgs = Forms!frmNew_Case_Match_Results.OpenArgs
varArg = Split(strOpenArgs, ",", , vbTextCompare)

strFN = CStr(varArg(0))
strLN = CStr(varArg(1))
varDOB = varArg(2)
varTest = varArg(3)

Me.[txtFN] = strFN
Me.[txtLN] = strLN
strLikeName = Left(Me.[txtLN], 1), this is then the value for a control
Me.[txtLikeName] = Nz(strLikeName, "")

Depending on the value using a Case statement in the OnLoad event, a strSQL
string is loaded for a cbo.rowsource showing clients and ID of matching
criteria. This works fine except with the like last name criteria.

It is supposed to load all clients with the same first letter of the last
name.

If last name is entered as 'N' all last names with the letter N show in cbo
fine.
If last name is entered as 'Na' only last names with the Na show in cbo.
If last name is entered as 'Names' no names show in cbo.

I cannot figure out the problem.

Here is the strSQL used for the Like Criteria:
***************************************
strSearch = Nz(Me.txtLikeName.Value, "")
......
Case Is = 5
strSQL = "SELECT [CDID], [CFID], [FN] & ' ' & [LN], [LN] "
strSQL = strSQL & "FROM tblClient_Details "
strSQL = strSQL & "WHERE [LN] Like ='" & strSearch & "*' "
strSQL = strSQL & "GROUP BY [CDID], [CFID], [FN] & ' ' & [LN], [LN]
"
strSQL = strSQL & "ORDER BY [LN];"

What is the problem?

-TFTH
-Bryan

 

[Bryan Hughes]

Ooops! I meant to say the following instead.

I have a problem with a combo box that is baffling me.

I have a new client entry form that checks for matching criteria before
continuing. If matching criteria is found, user is informed and can open
another form to view matching results. The criteria match is (Full Name,
Last Name, DOB, Like Last Name).

In the OnOpen event, critera is passed in OpenArgs, then it is referenced
and converted into appropraite date types.
Then placed into textbox controls.

Part of the OnOpen event is :
****************************************************
strOpenArgs = Forms!frmNew_Case_Match_Results.OpenArgs
varArg = Split(strOpenArgs, ",", , vbTextCompare)

strFN = CStr(varArg(0))
strLN = CStr(varArg(1))
varDOB = varArg(2)
varTest = varArg(3)

Me.[txtFN] = strFN
Me.[txtLN] = strLN
strLikeName = Left(Me.[txtLN], 1), this is then the value for a control
Me.[txtLikeName] = Nz(strLikeName, "")

**********************************************************
OnLoad event:

Depending on the value using a Case statement in the OnLoad event, a strSQL
string is loaded for a cbo.rowsource showing clients and ID of matching
criteria. This works fine except with the like last name criteria.

It is supposed to load all clients with the same first letter of the last
name.

If last name is entered as 'N' all last names with the letter N show in cbo
fine.
If last name is entered as 'Na' only last names with the Na show in cbo.
If last name is entered as 'Names' no names show in cbo.

I cannot figure out the problem.

Here is the strSQL used for the Like Criteria:
***************************************
strSearch = Nz(Me.txtLikeName.Value, "")
......
Case Is = 5
strSQL = "SELECT [CDID], [CFID], [FN] & ' ' & [LN], [LN] "
strSQL = strSQL & "FROM tblClient_Details "
strSQL = strSQL & "WHERE [LN] Like ='" & strSearch & "*' "
strSQL = strSQL & "GROUP BY [CDID], [CFID], [FN] & ' ' & [LN], [LN]
"
strSQL = strSQL & "ORDER BY [LN];"

What is the problem?

-TFTH
-Bryan

 

[Ben]

Bryan -
If I'm reading you correctly the problem is here:

It is supposed to load all clients with the same first letter of the last
name.

When 'Names' is entered, the query criteria becomes = 'Names*', if there
is no data that meet that criteria you get nothing. If you want all
records where the first letter is the same, change the code where you
set strSearch to:

strSearch = Ns(Left(Me.txtLikeName.Value, 1), "")

This will return only the first character and all names beginning with
that character will be returned by your query.

HTH,
Ben

Hello,

I have a problem with a combo box that is baffling me.

I have a new client entry form that checks for matching criteria before
continuing. If matching criteria is found, user is informed and can open
another form to view matching results.

In the matching results form openargs are passed from first form and
separated and converted into appropriate data types.

In On_Load event, evaluates criteria and assigns a value, depending on the
criteria match (Full Name, Last Name, DOB, Like Last Name).

Part of the On_Load event is :
****************************************************
strOpenArgs = Forms!frmNew_Case_Match_Results.OpenArgs
varArg = Split(strOpenArgs, ",", , vbTextCompare)

strFN = CStr(varArg(0))
strLN = CStr(varArg(1))
varDOB = varArg(2)
varTest = varArg(3)

Me.[txtFN] = strFN
Me.[txtLN] = strLN
strLikeName = Left(Me.[txtLN], 1), this is then the value for a control
Me.[txtLikeName] = Nz(strLikeName, "")

Depending on the value using a Case statement in the OnLoad event, a strSQL
string is loaded for a cbo.rowsource showing clients and ID of matching
criteria. This works fine except with the like last name criteria.

It is supposed to load all clients with the same first letter of the last
name.

If last name is entered as 'N' all last names with the letter N show in cbo
fine.
If last name is entered as 'Na' only last names with the Na show in cbo.
If last name is entered as 'Names' no names show in cbo.

I cannot figure out the problem.

Here is the strSQL used for the Like Criteria:
***************************************
strSearch = Nz(Me.txtLikeName.Value, "")
.....
Case Is = 5
strSQL = "SELECT [CDID], [CFID], [FN] & ' ' & [LN], [LN] "
strSQL = strSQL & "FROM tblClient_Details "
strSQL = strSQL & "WHERE [LN] Like ='" & strSearch & "*' "
strSQL = strSQL & "GROUP BY [CDID], [CFID], [FN] & ' ' & [LN], [LN]
"
strSQL = strSQL & "ORDER BY [LN];"

What is the problem?

-TFTH
-Bryan

 

[Van T. Dinh]

You used both = and Like. Use Like only.

Also, try changing the strSearch to:

strSearch = Left$(Nz(Me.txtLikeName.Value, ""), 1)