DLookUp help

[Guest]

I have read through Allen Browne's DLookUp page. I am still having issues
with DLookUp though. Mainly in the formatting. I have a Table called Main
that has 2 fields called Month_End and Month_End_Total. On a tabular form I
have 2 unbound text boxes called MonthEnd and MonthEndTotal. What I am
attempting to do is to pull a value from Month_End_Total in Main, and place
that value in MonthEndTotal on the form where Month_End in the Table is equal
to MonthEnd on the form. Here is the code I have:

Private Sub MonthlyFill()
Dim strWhere As Variant
Dim ReportMonth As Integer
Dim ReportYear As Integer

ReportMonth = InputBox("Enter the Report Month:")
ReportYear = InputBox("Enter the Report Year:")

[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)

strWhere = [MonthEnd]
[MonthEndTotal] = DLookup("Month_End_Total", "Main", "Month_End =" &
strWhere)
End Sub

It runs but never returns a value. I have multiple values entered into the
table, and have checked many times to make sure that I am inputing a value
that corresponds. It still pulls a null value. Any help would be greatly
appreciated.

C_Ascheman

 

[Guest]

Using this line:
[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)
you are assigning a string value ot [MonthEnd]. I am assuming, therefore,
the field Month_End in your table is a text field. Your DLookup is using
syntax for a numberic field. The correct syntax is:

[MonthEndTotal] = DLookup("[Month_End_Total]", "Main", "[Month_End] = '" &
strWhere) & "'"

Also notice the bracketing around the field names. This is always a good
idea.

 

[Guest]

I changed it as you said Klatuu. It is now throwing a runtime error 3075 -
Syntax error in string in query expression '[Month_End] = '2/2006'. I have
looked at it, and for the life of me cannot figure out why it is doing this.
The Month_End from the Table Main is set to text. I just can not seem to
figure out how to get the value of Month_End_Total from the Table Main into
the textbox MonthEndTotal on my form where Month_End from Table Main is equal
to strWhere. This is the only thing holding the program I am working on from
going forwards. Please if you anyone can help I will be greatly thankful.

C_Ascheman

Using this line:
[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)
you are assigning a string value ot [MonthEnd]. I am assuming, therefore,
the field Month_End in your table is a text field. Your DLookup is using
syntax for a numberic field. The correct syntax is:

[MonthEndTotal] = DLookup("[Month_End_Total]", "Main", "[Month_End] = '" &
strWhere) & "'"

Also notice the bracketing around the field names. This is always a good
idea.

I have read through Allen Browne's DLookUp page. I am still having issues
with DLookUp though. Mainly in the formatting. I have a Table called Main
that has 2 fields called Month_End and Month_End_Total. On a tabular form I
have 2 unbound text boxes called MonthEnd and MonthEndTotal. What I am
attempting to do is to pull a value from Month_End_Total in Main, and place
that value in MonthEndTotal on the form where Month_End in the Table is equal
to MonthEnd on the form. Here is the code I have:

Private Sub MonthlyFill()
Dim strWhere As Variant
Dim ReportMonth As Integer
Dim ReportYear As Integer

ReportMonth = InputBox("Enter the Report Month:")
ReportYear = InputBox("Enter the Report Year:")

[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)

strWhere = [MonthEnd]
[MonthEndTotal] = DLookup("Month_End_Total", "Main", "Month_End =" &
strWhere)
End Sub

It runs but never returns a value. I have multiple values entered into the
table, and have checked many times to make sure that I am inputing a value
that corresponds. It still pulls a null value. Any help would be greatly
appreciated.

C_Ascheman

 

[Guest]

Post back with the code you have now.
Would 2/2006 be a correct value in [Month_End]?

I changed it as you said Klatuu. It is now throwing a runtime error 3075 -
Syntax error in string in query expression '[Month_End] = '2/2006'. I have
looked at it, and for the life of me cannot figure out why it is doing this.
The Month_End from the Table Main is set to text. I just can not seem to
figure out how to get the value of Month_End_Total from the Table Main into
the textbox MonthEndTotal on my form where Month_End from Table Main is equal
to strWhere. This is the only thing holding the program I am working on from
going forwards. Please if you anyone can help I will be greatly thankful.

C_Ascheman

Using this line:
[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)
you are assigning a string value ot [MonthEnd]. I am assuming, therefore,
the field Month_End in your table is a text field. Your DLookup is using
syntax for a numberic field. The correct syntax is:

[MonthEndTotal] = DLookup("[Month_End_Total]", "Main", "[Month_End] = '" &
strWhere) & "'"

Also notice the bracketing around the field names. This is always a good
idea.

I have read through Allen Browne's DLookUp page. I am still having issues
with DLookUp though. Mainly in the formatting. I have a Table called Main
that has 2 fields called Month_End and Month_End_Total. On a tabular form I
have 2 unbound text boxes called MonthEnd and MonthEndTotal. What I am
attempting to do is to pull a value from Month_End_Total in Main, and place
that value in MonthEndTotal on the form where Month_End in the Table is equal
to MonthEnd on the form. Here is the code I have:

Private Sub MonthlyFill()
Dim strWhere As Variant
Dim ReportMonth As Integer
Dim ReportYear As Integer

ReportMonth = InputBox("Enter the Report Month:")
ReportYear = InputBox("Enter the Report Year:")

[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)

strWhere = [MonthEnd]
[MonthEndTotal] = DLookup("Month_End_Total", "Main", "Month_End =" &
strWhere)
End Sub

It runs but never returns a value. I have multiple values entered into the
table, and have checked many times to make sure that I am inputing a value
that corresponds. It still pulls a null value. Any help would be greatly
appreciated.

C_Ascheman

 

[Guest]

Thanks for you help Klatuu. I got it working. Here is the code now:

[MonthEndTotal] = DLookup("[Month_End_Total]", "Main", "[Month_End] = '" &
strWhere & "'")

After reading your post I decided to do a search for DLookup on the general
web which brought me to a Microsoft help article on DLookup. Between that,
and what you posted I figured it out. Much thanks to you.

C_Ascheman

Post back with the code you have now.
Would 2/2006 be a correct value in [Month_End]?

I changed it as you said Klatuu. It is now throwing a runtime error 3075 -
Syntax error in string in query expression '[Month_End] = '2/2006'. I have
looked at it, and for the life of me cannot figure out why it is doing this.
The Month_End from the Table Main is set to text. I just can not seem to
figure out how to get the value of Month_End_Total from the Table Main into
the textbox MonthEndTotal on my form where Month_End from Table Main is equal
to strWhere. This is the only thing holding the program I am working on from
going forwards. Please if you anyone can help I will be greatly thankful.

C_Ascheman

Using this line:
[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)
you are assigning a string value ot [MonthEnd]. I am assuming, therefore,
the field Month_End in your table is a text field. Your DLookup is using
syntax for a numberic field. The correct syntax is:

[MonthEndTotal] = DLookup("[Month_End_Total]", "Main", "[Month_End] = '" &
strWhere) & "'"

Also notice the bracketing around the field names. This is always a good
idea.


:

I have read through Allen Browne's DLookUp page. I am still having issues
with DLookUp though. Mainly in the formatting. I have a Table called Main
that has 2 fields called Month_End and Month_End_Total. On a tabular form I
have 2 unbound text boxes called MonthEnd and MonthEndTotal. What I am
attempting to do is to pull a value from Month_End_Total in Main, and place
that value in MonthEndTotal on the form where Month_End in the Table is equal
to MonthEnd on the form. Here is the code I have:

Private Sub MonthlyFill()
Dim strWhere As Variant
Dim ReportMonth As Integer
Dim ReportYear As Integer

ReportMonth = InputBox("Enter the Report Month:")
ReportYear = InputBox("Enter the Report Year:")

[MonthEnd] = ((ReportMonth - 1) & "/" & ReportYear)

strWhere = [MonthEnd]
[MonthEndTotal] = DLookup("Month_End_Total", "Main", "Month_End =" &
strWhere)
End Sub

It runs but never returns a value. I have multiple values entered into the
table, and have checked many times to make sure that I am inputing a value
that corresponds. It still pulls a null value. Any help would be greatly
appreciated.

C_Ascheman