Display Text In a Combo Box bound to Numeric Field

[matomo]

Hi

Is there a way to display a text field that is bound to a numberic field in a combo box when the text field hav a numeric value of Null

Table "tblOddEven

Field 1 (Text) Field 2 (Numeric)
1. Both Null (Blank)
2. Even 0
3.Odd 1

If Both or Even is selected in the dropdown box on the form no records are returned in the report but ODD is seleted the records that are returned are even numbers not odd numbers.

For example: 1 - 20 Neepawa Ave Toronto [BOTH] return nothing
1 - 20 Neepawa Ave Toronto [Even] return nothing
1 - 20 Neepawa Ave Toronto [Odd] return even records.

What am I doing wrong.
Below is the function that runs my parameter query:

Function OddEven()
On Error GoTo OddEven_Err

Dim qdfTemp As DAO.QueryDef
Dim rstName As DAO.Recordset
Dim sqlstring As String
Dim str_MyStreetNumber As String
Dim str_MyStreetNumberEnd As String
Dim str_MyStreetName As String
Dim str_MyStreetType As String
Dim str_MyStreetDir As String
Dim str_MyMunicipal As String
Dim dbs As Database

Set dbs = CurrentDb

sqlstring = "SELECT * FROM [tblEVENTS3]"

Set rstName = dbs.OpenRecordset(sqlstring)
Do While Not rstName.EOF

If IsNull(rstName.Fields("OddEven")) Then

Set qdfTemp = dbs.QueryDefs("qryROLLNUMBOTH")

str_MyStreetNumber = rstName.Fields("StreetNumber")
str_MyStreetNumberEnd = rstName.Fields("StreetNumberEnd")
str_MyStreetName = rstName.Fields("StreetName")
str_MyStreetType = rstName.Fields("StreetType")
str_MyStreetDir = Nz(rstName.Fields("StreetDirection"), " ")
str_MyMunicipal = rstName.Fields("Municipal")

qdfTemp.Parameters("my_street_number") = str_MyStreetNumber
qdfTemp.Parameters("my_street_number_end") = str_MyStreetNumberEnd
qdfTemp.Parameters("my_street_name") = str_MyStreetName
qdfTemp.Parameters("my_street_type") = str_MyStreetType
qdfTemp.Parameters("my_street_dir") = str_MyStreetDir
qdfTemp.Parameters("my_municipal") = str_MyMunicipal

qdfTemp.Execute
qdfTemp.Close
Else
Set qdfTemp = dbs.QueryDefs("qryROLLNUM")

str_MyStreetNumber = rstName.Fields("StreetNumber")
str_MyStreetNumberEnd = rstName.Fields("StreetNumberEnd")
str_MyStreetName = rstName.Fields("StreetName")
str_MyStreetType = rstName.Fields("StreetType")
str_MyStreetDir = Nz(rstName.Fields("StreetDirection"), " ")
str_MyMunicipal = rstName.Fields("Municipal")

qdfTemp.Parameters("my_street_number") = str_MyStreetNumber
qdfTemp.Parameters("my_street_number_end") = str_MyStreetNumberEnd
qdfTemp.Parameters("my_street_name") = str_MyStreetName
qdfTemp.Parameters("my_street_type") = str_MyStreetType
qdfTemp.Parameters("my_street_dir") = str_MyStreetDir
qdfTemp.Parameters("my_municipal") = str_MyMunicipal

If rstName.Fields("OddEven") = 1 Then
qdfTemp.Parameters("odd_even") = 1
Else
qdfTemp.Parameters("odd_even") = 0

End If

qdfTemp.Execute
DoCmd.Save
qdfTemp.Close

End If

rstName.MoveNext

Loop

OddEven_Exit:
Exit Function

OddEven_Err:
MsgBox Error$
Resume OddEven_Exit

End Function

Thanks in advance.

matomo

 

[Oldsoftboss]

I would use a select case to find which set of data you want to return and maybe the Mod function to get odd or even No's

Dave

 

[Oldsoftboss]

Maybe this will help.
I have not touched your table structure, but it could do with some work.

Dave

 

[Oldsoftboss]

Hi

Is there a way to display a text field that is bound to a numberic field in a combo box when the text field hav a numeric value of Null

Table "tblOddEven

Field 1 (Text) Field 2 (Numeric)
1. Both Null (Blank)
2. Even 0
3.Odd 1

If you have a look at the combo box (set the first column width to 0.3) you will see that

1. = Even
2. = Odd
3. = Both

Dave

 

[matomo]

Oldsoftboss;

Thanks, I have tried your suggestion but nothing works.

What are my doing wrong. Please explain further.

Thanks.

matomo

 

[Oldsoftboss]

Oldsoftboss;

Thanks, I have tried your suggestion but nothing works.

What are my doing wrong. Please explain further.

Thanks.

matomo

In the example I posted back...

?? enter a low street no (1), enter a high street no (40), select a street fom the combo box (Florist) and hit Print Report and about 40 results are returned. Selecting Odd or Even will filter even further.

Dave

 

[matomo]

Oldsoftboss;

Thanks for your valuable help. It works perfectly. I have studied your query it is exactly what I was trying to do.

Thanks again. You are an officer and a gentleman.

matomo

 

[Oldsoftboss]

And hardly any code now !!

Dave