Input Boxes not valid for Where Expressions?

[believeinblue44]

I'm new to VB and attempting something (seemingly very very basic by normal standards) and I can not seem to get my Where expression to read values from my inputbox. This is what i have and i am recieving an error sayings "The Microsoft Jet Database engine does not recognize 'x' as a valid field name or expression"

Dim x As Integer
Dim y As Integer

strFirstvalue = InputBox("Enter Starting Value", "Starting Value", "0")
strSecondvalue = InputBox("Enter Ending Value", "Ending Value", "100")

x = strFirstvalue
y = strSecondvalue

DoCmd.OpenReport "MTBF", acViewPreview, , "[Serial Number] Between x and y", acWindowNormal, " "


any simple fixes?

[puppydogbuddy]

I'm new to VB and attempting something (seemingly very very basic by normal standards) and I can not seem to get my Where expression to read values from my inputbox. This is what i have and i am recieving an error sayings "The Microsoft Jet Database engine does not recognize 'x' as a valid field name or expression"

Dim x As Integer
Dim y As Integer

strFirstvalue = InputBox("Enter Starting Value", "Starting Value", "0")
strSecondvalue = InputBox("Enter Ending Value", "Ending Value", "100")

x = strFirstvalue
y = strSecondvalue

DoCmd.OpenReport "MTBF", acViewPreview, , "[Serial Number] Between x and y", acWindowNormal, " "


any simple fixes?

Yes, use the Val function to convert x and y from string to numeric as shown:

x = Val(strFirstvalue)
y = Val(strSecondvalue)

[believeinblue44]

I've tried all different ways of putting x as a val, using val of the string and all return the exact same error. I just attempted your idea of x = Val( etc.) and it again returned that x isn't a valid field or expression

I dont understand it because when i just enter values like 20 and 30 instead of x and y it comprehends and runs the where expression, how is x=Val any different than just a typed in constant number

[puppydogbuddy]

I've tried all different ways of putting x as a val, using val of the string and all return the exact same error. I just attempted your idea of x = Val( etc.) and it again returned that x isn't a valid field or expression

Check the return value from your input box. What does it show?

[puppydogbuddy]

Check the return value from your input box. What does it show?

Try it this way:

strFirstvalue = InputBox("Enter Starting Value")
strSecondvalue = InputBox("Enter Ending Value")

x = Val(strFirstvalue)
y = Val(strSecondvalue)

[believeinblue44]

I'm not entirely sure what you mean by that? I can use the x and y values in other expressions (i did that to make sure my input box was reading the values i type in).

[believeinblue44]

I'm not entirely sure what you mean by that? I can use the x and y values in other expressions (i did that to make sure my input box was reading the values i type in).

EDIT: I tried your above post with shortening the inputbox code and same error occured

[puppydogbuddy]

I'm not entirely sure what you mean by that? I can use the x and y values in other expressions (i did that to make sure my input box was reading the values i type in).

I see what your problem is. You have the wrong syntax for the InputBox. Note syntax of line with x and y in the example.

From Access On-line Help:

InputBox Function Example

The following example uses the InputBox function to return the user's name. Note that you can't open a Help file from a dialog box created by the InputBox function in Microsoft Access.

Sub Greeting()
    Dim strInput As String, strMsg As String
 
    strMsg = "Enter your name."
    strInput = InputBox(Prompt:=strMsg, _
        Title:="User Info", XPos:=2000, YPos:=2000)
    MsgBox "Hello, " & strInput
End Sub
 

[believeinblue44]

Ha ha...well thanks for the help, thought it was going to work but i still got the exact same error: Not accepting x or y as fields or expressions. I have a feeling it isnt in the code but rather access not wanting to read variables.

[puppydogbuddy]

Ha ha...well thanks for the help, thought it was going to work but i still got the exact same error: Not accepting x or y as fields or expressions. I have a feeling it isnt in the code but rather access not wanting to read variables.

No, it is your syntax. Try syntax below as is:

Dim x As Integer
Dim y As Integer
 
strFirstvalue = InputBox("Enter Starting Value", "Starting Value", x = 0)
strSecondvalue = InputBox("Enter Ending Value", "Ending Value", y= 100)
 
 
DoCmd.OpenReport "MTBF", acViewPreview, , "[Serial Number] Between " & x & " and " & y, acWindowNormal, " "
 

[believeinblue44]

Thank you! My error's gone, i'm getting calculation errors but i can handle those i'm sure. Thanks for staying with me on that

[puppydogbuddy]

Thank you! My error's gone, i'm getting calculation errors but i can handle those i'm sure. Thanks for staying with me on that

No problem. Be sure and let me know if you need more help.