The following code is supposed to grab the PO Amt and group the data with the date parameters from a form. However, the coded finds no data. If the date parameter from the form is removed, the code will find data to group.
Any suggestions would be appreciated.
Mary -
Private Sub sql_Step04_b1_Extract_POScreenAmt()
-
-
On Error GoTo Err_Hndlr
-
-
'**********************************************
-
Dim dbs As Database
-
Dim strSQL As String
-
Dim strQueryName As String
-
Dim qryDef As QueryDef
-
-
'set variable values
-
Set dbs = CurrentDb
-
strQueryName = "sql_Step04_b1_Extract_POScreenAmt"
-
-
'Delete old query first - we want fresh data!
-
dbs.QueryDefs.Delete strQueryName
-
-
'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
-
-
-
strSQL = "SELECT POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) AS [Creation Date2]" & _
-
"FROM POCompletedScreen " & _
-
"GROUP BY POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) " & _
-
"HAVING (((DateValue([Creation Date])) Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]))" & _
-
"ORDER BY POCompletedScreen.[PO #];"
-
-
'Create query definition
-
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
-
-
-
-
sql_Step04_b1_Extract_POScreenAmt_Exit:
-
Exit Sub
-
-
-
Err_Hndlr:
-
MsgBox "[" & Err.Number & "]: " & Err.Description, vbInformation, "sql_Step04_b1_Extract_POScreenAmt()"
-
End Sub
-
From what i can gather, the vba statement looks at the form parameter as a string therefore, it needs to be qualified as such: -
Between #" & [Forms]![F_Waiver_Yr]![txb_date_start] # And #" & [Forms]![F_Waiver_Yr]![txb_date_end] & "#
-
14 2258
in you HAVING clause, did you try putting hard coded dates to see if it returns someting?
I am not sure about using parameter values, but normally, dates must be enclosed between #. Other wise something like 2010-03-04 will do 2010 minus 3 minus 4...
Yes, i tested with hard coded dates and it worked. however, the dates are variable.
After you define strSQL, can you put a line
so you/we can see exactly what the query looks like after you enter the parameters in the form.
the output will be visible in the Immediate Window. You can see it by doing Ctrl+g.
If the query does not return any error, just blank output, it is the having clause that basically eliminates all records...
in the immediate window, the -
?[Forms]![F_Waiver_Yr]![txb_date_start]
-
?[Forms]![F_Waiver_Yr]![txb_date_end]
-
returns, the selected date of 06/01/2010 and 06/30/2010.
this is what the debug.print returns for the SQL statement: -
SELECT POCompletedScreen.[PO #], POCompletedScreen.[PO Total], DateValue([Creation Date]) AS [Creation Date2]FROM POCompletedScreen GROUP BY POCompletedScreen.[PO #], POCompletedScreen.[PO Total], DateValue([Creation Date]) HAVING (((DateValue([Creation Date])) Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]))ORDER BY POCompletedScreen.[PO #];
-
Ah no wonder, it is like the [Form]... statements are taken as a pure string, you want that: -
strSQL = "SELECT POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) AS [Creation Date2]" & _
-
"FROM POCompletedScreen " & _
-
"GROUP BY POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) " & _
-
"HAVING (((DateValue([Creation Date])) Between" & _
-
[Forms]![F_Waiver_Yr]![txb_date_start] & " And " & _
-
[Forms]![F_Waiver_Yr]![txb_date_end] & ")) ORDER BY POCompletedScreen.[PO #];"
-
i'm not following you, you are implying that "[Forms]![F_Waiver_Yr]![txb_date_start]" converts itself into a string.
however, i have a similar statement works which is executed prior to this sql statement. the other code is as follows: -
Private Sub sql_Step01_a_PO_MONTH()
-
-
On Error GoTo Err_Hndlr
-
-
'**********************************************
-
Dim dbs As Database
-
Dim strSQL As String
-
Dim strQueryName As String
-
Dim qryDef As QueryDef
-
-
'set variable values
-
Set dbs = CurrentDb
-
strQueryName = "sql_Step01_a_PO_MONTH"
-
-
'Delete old query first - we want fresh data!
-
dbs.QueryDefs.Delete strQueryName
-
-
'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
-
-
-
strSQL = "SELECT FIMSMGR_FPBPOHD.FPBPOHD_CODE, FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE, FIMSMGR_FPBPOHD.FPBPOHD_PO_DATE, FIMSMGR_FPBPOHD.FPBPOHD_CHANGE_SEQ_NUM, FIMSMGR_FPBPOHD.FPBPOHD_PCLS_CODE, FIMSMGR_FPBREQH.FPBREQH_DOC_REF_CODE, FIMSMGR_FPBPOHD.FPBPOHD_BUYR_CODE, FIMSMGR_FPRPODT.FPRPODT_COMM_DESC, Sum(FIMSMGR_FPRPODA.FPRPODA_AMT) AS SumOfFPRPODA_AMT, FIMSMGR_FPRPODT.FPRPODT_COMM_CODE " & _
-
"FROM FIMSMGR_FPRPODA " & _
-
"INNER JOIN (FIMSMGR_FPRPODT INNER JOIN " & _
-
"((FIMSMGR_FTVRQPO RIGHT JOIN FIMSMGR_FPBPOHD ON FIMSMGR_FTVRQPO.FTVRQPO_POHD_CODE = FIMSMGR_FPBPOHD.FPBPOHD_CODE) " & _
-
"LEFT JOIN FIMSMGR_FPBREQH ON FIMSMGR_FTVRQPO.FTVRQPO_REQD_CODE = FIMSMGR_FPBREQH.FPBREQH_CODE) " & _
-
"ON FIMSMGR_FPRPODT.FPRPODT_POHD_CODE = FIMSMGR_FPBPOHD.FPBPOHD_CODE) " & _
-
"ON (FIMSMGR_FPRPODA.FPRPODA_POHD_CODE = FIMSMGR_FPRPODT.FPRPODT_POHD_CODE) " & _
-
"AND (FIMSMGR_FPRPODA.FPRPODA_POHD_CODE = FIMSMGR_FPBPOHD.FPBPOHD_CODE) " & _
-
"AND (FIMSMGR_FPRPODA.FPRPODA_CHANGE_SEQ_NUM = FIMSMGR_FPBPOHD.FPBPOHD_CHANGE_SEQ_NUM) AND (FIMSMGR_FPRPODA.FPRPODA_CHANGE_SEQ_NUM = FIMSMGR_FPRPODT.FPRPODT_CHANGE_SEQ_NUM) " & _
-
"GROUP BY FIMSMGR_FPBPOHD.FPBPOHD_CODE, FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE, FIMSMGR_FPBPOHD.FPBPOHD_PO_DATE, FIMSMGR_FPBPOHD.FPBPOHD_CHANGE_SEQ_NUM, FIMSMGR_FPBPOHD.FPBPOHD_PCLS_CODE, FIMSMGR_FPBREQH.FPBREQH_DOC_REF_CODE, FIMSMGR_FPBPOHD.FPBPOHD_BUYR_CODE, FIMSMGR_FPRPODT.FPRPODT_COMM_DESC, FIMSMGR_FPRPODT.FPRPODT_COMM_CODE, FIMSMGR_FPRPODT.FPRPODT_ITEM " & _
-
"HAVING (((FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE) Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]) AND ((FIMSMGR_FPRPODT.FPRPODT_ITEM)=1))" & _
-
"ORDER BY FIMSMGR_FPBPOHD.FPBPOHD_TRANS_DATE DESC;"
-
-
-
'Create query definition
-
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
-
-
-
-
sql_Step01_a_PO_MONTH_Exit:
-
Exit Sub
-
-
-
Err_Hndlr:
-
MsgBox "[" & Err.Number & "]: " & Err.Description, vbInformation, "sql_Step01_a_PO_MONTH()"
-
End Sub
-
Ok you can disregard my last, it works differently when you use a query def than when you use a domcd.runsql which I am used to.
Not sure now why it would not work at the moment. I will sleep on that tonight.
You are missing #'s to define your start and end date. - strSQL = "SELECT POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) AS [Creation Date2]" & _
-
"FROM POCompletedScreen " & _
-
"GROUP BY POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) " & _
-
"HAVING (((DateValue([Creation Date])) Between #[Forms]![F_Waiver_Yr]![txb_date_start]# And #[Forms]![F_Waiver_Yr]![txb_date_end]#))" & _
-
"ORDER BY POCompletedScreen.[PO #];"
nope, the # has no affect on it. i tried and got the same results. moreover, it works on the other sql statements.
Can you tell me a bit about the [Creation Date] you used in the DateValue? What format is inside this field? The problem might comes from it.
Good morning,
The [Creation date] is a date type field. Originally, it was a string but is was converted in the back-end.
From what i can gather, the vba statement looks at the form parameter as a string therefore, it needs to be qualified as such: -
Between #" & [Forms]![F_Waiver_Yr]![txb_date_start] # And #" & [Forms]![F_Waiver_Yr]![txb_date_end] & "#
-
When you look into the [Creation date] from the table view, is it simply in the format of mm/dd/yyyy?
Normally by default, SQL recogned date fields as mm/dd/yyyy format, so is your [Creation date] originally a DATE/TIME datatype? if so then you try the code again without DateValue
the DateValue was dropped after, the code was corrected with the above which is re-posted below. i agree less code the more efficient. thank you for your assistance.
mary -
Between #" & [Forms]![F_Waiver_Yr]![txb_date_start] # And #" & [Forms]![F_Waiver_Yr]![txb_date_end] & "#
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Abdul Mohsin |
last post by:
is it possible to get hidden parameter values using
window.opener.document.form.parameter.value.
I am getting this error " Object not found"
Please help
Abdul
|
by: Chuck |
last post by:
I am writing a database program in C#.
I have a situation when I type the '(' after typing a method call, VS goes
into an infinite loop. I then have to exit VS.
Has anyone else had any trouble...
|
by: Jurij P |
last post by:
I have a problem.
I would like to change a value from textbox with JavaScript before I submit
the form (submit updated value). Can anyone please help me?
thank you, Jure
|
by: Mike Bridge |
last post by:
I'm getting a nonsensical error when binding a DataTable with one row
to a DropDownList.
The DataTable's row contains the fields "id" (INT), "description"
(VARCHAR) and with a couple other...
|
by: Ale K. |
last post by:
What's the best way to make a form return a value.... i want to try to avoid
using module variables for doing this...
Thanks.
Alex.
|
by: Lisa J. |
last post by:
Heloe
I would like to be able to pass a condition of an IF statement as parameter as follow
aStr=” (A=1) and (B=2)”
if aFunc(“aStr”) then
….
End if
|
by: darrel |
last post by:
What does this error mean?
Specified argument was out of the range of valid values. Parameter name:
value
System.Web
It's being thrown here:
DDL_SubCategory.Enabled = True...
|
by: thisis |
last post by:
Hi All,
myGetImage.asp is suppose to create a temporary file in the client
side,
and display in on the client web browser.
myGetImage.asp needs to know the clients web browser temporary...
|
by: Dean Slindee |
last post by:
I have a form whose Property value I need to get at from a class (contained
in another project, same solution).
Here is the form's property:
Private booIsInsert As Boolean = False
Public...
|
by: dima69 |
last post by:
Hi all.
Here is a problem. I want to sort a form by absolute value. Let's say, if I have a field named "theSum", I'd like to set the form OrderBy property to "Abs()". If I use "Advanced...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing,...
| |