VBA Controlled form - Unable to use multiple unbound field criteria

[TanyaCVV]

Hello,

I have a database, within in are multiple queries and reports. I have a form. This form is meant to utilize VBA code that pulls from 2 tables which have been created to tell the unbound field within the form how and what information can be pulled to produce a report, these fields are required to add the ability to filter a report by date, customer or product code. I am able to run most with the first criteria/unbound field, as soon as I attempt to utilize more than 1 criteria I receive error 3075 for example when using the daily sales report it will run with criteria 1 = the date but if I add criteria t - the customer name I get this erroe.. When using the report SalesByDateByProductCode I am getting error 2501.



I am not strong with VBA, so I am unable to figure out why this is not working. I would be greatly appreciative of some help in finding the right code to make this work.

The control tables are AppSysListOutputType, AppSysReportControls and AppSysReports.

The form in question is frmDldRpts

The specific reports I am having issues with are rptSalesDetailbyProductCode, rptDailySalesDetailbyGP

This is tied to our accounting database so the data will not be there to test from my upload as it is a "linked" VBA CODE.txtdatabase, but I am hoping by viewing the form and report setup and the control tables and VBA someone may be able to help asisst me.

Access database is to large to upload.VBA CODE.txt

[CJ_London]

I am not strong with VBA, so I am unable to figure out why this is not working. I would be greatly appreciative of some help in finding the right code to make this work.

what code are you trying to use that's 'not working' - copy and paste the code from your vba module to your post then surround with the code tags (highlight pasted text and click the code button (the # one).

Also what is the description of error 2501?

[TanyaCVV]

Run-time error '3075':
Syntax error in date in query expression '(TxnDate="2019-09-01" AND TxnDate=#2019-09-30# AND CustomerRefFullName=#CASH#'.
rptDailySalesDetailbyGP

Run-time error '3075':
Syntax error in date in query expression '(TxnDate="2019-09-01" AND TxnDate=#2019-09-30# AND CustomFieldProductCode=#STORE#'.
rptSalesDetailbyProductCode
So now I am only getting the 3075 error as shown above for both

Here is the entire VBA code

Option Compare Database

Private Sub cboReport_AfterUpdate()


If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")) = False Then

Me.cboCrit1.Visible = True

Me.cboCrit1.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")

Me.lblCrit1.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")

Me.cboCrit1.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")

Else

Me.cboCrit1.Visible = False

End If

If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")) = False Then

Me.cboCrit2.Visible = True

Me.cboCrit2.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")

Me.lblCrit2.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")

Me.cboCrit2.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")

Else

Me.cboCrit2.Visible = False

End If

If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")) = False Then

Me.cboCrit3.Visible = True

Me.cboCrit3.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

Me.lblCrit3.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

Me.cboCrit3.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")

Else

Me.cboCrit3.Visible = False

End If


Me.lblDetails.Caption = Me.cboReport.Column(2)

Me.cboPrintMethod = 1


End Sub

Private Sub cmdClearCrit_Click()


Me.cboCrit1.Value = ""

Me.cboCrit2.Value = ""

Me.cboCrit3.Value = ""


End Sub

Private Sub Ctl_cmdPrint_Click()


Dim strCrit As String

If Me.cboCrit1 <> "" Then

strCrit = Me.cboCrit1.Tag & "=""" & Me.cboCrit1.Value & """"

End If

If Me.cboCrit2 <> "" Then

If strCrit = "" Then

strCrit = Me.cboCrit2.Tag & "=#" & Me.cboCrit2 & "#"

Else

strCrit = strCrit & " AND " & Me.cboCrit2.Tag & "=#" & Me.cboCrit2.Value & "#"

End If

End If

If Me.cboCrit3 <> "" Then

If strCrit = "" Then

strCrit = Me.cboCrit3.Tag & "=#" & Me.cboCrit3 & "#"

Else

strCrit = strCrit & " AND " & Me.cboCrit3.Tag & "=#" & Me.cboCrit3.Value & "#"

End If

End If


If Me.cboPrintMethod = 1 Then


DoCmd.OpenReport Me.cboReport.Column(3), acViewPreview, , strCrit



Else


DoCmd.OpenReport Me.cboReport.Column(3), acViewNormal, , strCrit


End If


DoCmd.Close acForm, "frmDlgRpts"




End Sub


The red text is what is showing as highlighted when I select Debug and go to the VBA to fix the issue

[ssanfu]

Run-time error '3075':
Syntax error in date in query expression '(TxnDate="2019-09-01" AND TxnDate=#2019-09-30# AND CustomerRefFullName=#CASH#'.
rptDailySalesDetailbyGP

Run-time error '3075':
Syntax error in date in query expression '(TxnDate="2019-09-01" AND TxnDate=#2019-09-30# AND CustomFieldProductCode=#STORE#'.
rptSalesDetailbyProductCode

Breaking up the above snippet, you have
TxnDate="2019-09-01"
TxnDate=#2019-09-30#.
CustomFieldProductCode=#STORE#

1) Is "TxnDate" a Date/Time field type or is it a String Type? You have both delimiters for the field.
2) Is "CustomFieldProductCode" a Date/Time field type or is it a String Type? It looks like you have Date delimiters around a String type.
3) HOW can "TxnDate" be equal to 2019-09-01 at the same time it is equal to 2019-09-30?? (TxnDate="2019-09-01" AND TxnDate=#2019-09-30#)

Access database is to large to upload

Have you tried doing a "Compact and Repair" and then compressing the dB (Zip it)??



This is kinda confusing:

[TanyaCVV]

Your comment with the visual - the criteria are for different reports, please see column one. The form needs to pull different information for different reports.

I do not know how to utilize any of the information you stated below:

Breaking up the above snippet, you have
TxnDate="2019-09-01"
TxnDate=#2019-09-30#.
CustomFieldProductCode=#STORE#

1) Is "TxnDate" a Date/Time field type or is it a String Type? You have both delimiters for the field.
2) Is "CustomFieldProductCode" a Date/Time field type or is it a String Type? It looks like you have Date delimiters around a String type.
3) HOW can "TxnDate" be equal to 2019-09-01 at the same time it is equal to 2019-09-30?? (TxnDate="2019-09-01" AND TxnDate=#2019-09-30#)

Parion Reports2.zip

[Missinglinq]

I do not know how to utilize any of the information you stated below:

The point is that in some, possibly all cases you're using delimiters that are not appropriate for your Datatypes. In some cases you're even using two different delimiters for same Control/Field!

Each Datatype requires a different delimiter, when dealing with Explicit Values/Data:

• String Fields require Quotation Marks(" ")
• Date/Time Fields require Octothorps (# #)
• Numeric Fields require no delimiter

As for

3) HOW can "TxnDate" be equal to 2019-09-01 at the same time it is equal to 2019-09-30?? (TxnDate="2019-09-01" AND TxnDate=#2019-09-30#)

Obviously it can't be equal to two different Values at the same time!

Perhaps you meant

(TxnDate="2019-09-01" OR TxnDate=#2019-09-30#)

But once again...you have to have the same delimiters around the same Datatype, in this case apparently one that is Date/Time...if this is so it would be

(TxnDate=#2019-09-01# OR TxnDate=#2019-09-30#)

Linq ;0)>

[ssanfu]

OK, after looking at your dB, I understand better.

You have two different DATE requirements.
For report "rptDailySalesDetailbyGP", you are looking for for a single Date for TxnDate.

For report "rptSalesDetailbyProductCode" you are looking for dates BETWEEN two dates -> Start Date and End Date.
So you are going to have to modify the code in Private Sub Ctl_cmdPrint_Click()
You will have to determine if the control "cboCrit1" is visible or not when generating the variable "strCrit"

Here is a article by Allen Browne on setting up a search form.
And here is the code for the form.
This should help you , but if you have trouble getting it to work, post back and I'll gen something for you.



Also, instead of using

 If Me.cboCrit1 <> "" Then

to determine of the control has a value, I use

 If Len(Trim(Me.cboCrit1)) > 0 Then

Sometimes a control has a space in it. If the value of the control is a space, then the control will not be equal to "" (empty string).
Again, this is my personal preference...

[TanyaCVV]

Thank you everyone for your help. So I am almost there. I re-wrote the Code and now there are no errors BUT I am still not getting the right information. I am entering the Start and End Date and not getting the data from the range only from the start date.

I have attached a zip file with the updated information and code. Parion Reports2.zip

This is the Criteria I have in the query under "Txndate" that the form is pulling from and the report it is pulling is based from

Between [Forms]![frmDlgRpts]![cboCrit1] And [Forms]![frmDlgRpts]![cboCrit2]

[ssanfu]

I looked at what you did and I think you misunderstood (a little) on how to create the criteria.

I added "Option Explicit" at the top of the module, formatted your code and modified the sub "Ctl_cmdPrint_Click".

Because you are using SQL Server (??) as the BE, the relationships are not available. However, from what I could see, I would recommend you review your relationships. You have the PK field a text type and you do not link the tables using the PK field.
Look at the saved query "qselSalesLineGPbyDateByProductCode" and at the SQL

SELECT CreditMemoLine.*, Customer.AccountNumber, CreditMemoLine.TxnDate, Customer.PreferredDeliveryMethod, [Subtotal]+[SalesTaxTotal] AS Total, Year([TxnDate])*12+DatePart("m",[TxnDate]) AS Expr1 
FROM CreditMemoLine INNER JOIN Customer ON CreditMemoLine.BillAddressAddr1 = Customer.BillAddressAddr1 
WHERE (((Customer.PreferredDeliveryMethod)="Mail") AND ((Year([TxnDate])*12+DatePart("m",[TxnDate]))=Year(Date())*12+DatePart("m",Date())-1)) 
ORDER BY Customer.AccountNumber;

I recommend you read Microsoft Access Tables: Primary Key Tips and Techniques several times.




Data is duplicated, not only in 2 tables, but many tables. One example would be field "BillAddressAddr1" in the "Customer" table; it is in a lot of tables (along with many other duplicated fields).

There are also many reserved words used as field names. See Problem names and reserved words in Access
Here are a few of the field names that should be changed:
table ITEM
Name
Description
Type


table CUSTOMER
Name

table ACCOUNT
Name
Desc


table BILL
MEMO

table BillExpenseLine
MEMO


table BillingRateLine
Name

table BillItemLine
MEMO

table BillLinkedTxn
MEMO

table BillPaymentCheck
MEMO

table BillPaymentCheckLine
MEMO

table BillPaymentCheckLineLinkedTxn
MEMO

table Charge
Rate
Desc

table ChargeLinkedTxn
Rate
Desc

- didn't look at the other tables.



Try this dB to see if you get results...

[TanyaCVV]

This is all going far beyond my scope. I had thought creating a form would be much more simple. Unfortunately that file continued to go "not responding" and was not able to pull any results. I am connected to an ODBC database so I cannot change the names in the tables as they are directly linked and interactive with my Quickbooks software.

[ssanfu]

I forgot you were linking into an Accounting system - didn't know it was Quickbooks. That explains a lot - forget the renaming comments..... My apologies....

I haven't worked with Access/Quickbooks in over 8 years. I had to put data INTO the Quickbook tables to be able to print checks. Took a long time to get it correct (over a month of research and testing).

This is all going far beyond my scope. I had thought creating a form would be much more simple. Unfortunately that file continued to go "not responding"

Not sure what "File" you are referring to.


Questions:
-- If you manually open query qselSalesLineGPbyDateByProductCode , do you get results? Are the results correct?

If you create a temporary saved query (like qselSalesLineGPbyDateByProductCode) using the following SQL:

SELECT SalesLine.TxnDate, SalesLine.CustomerRefFullName, SalesLine.RefNumber, SalesLine.SalesLineDesc, IIf(IsNull([PurchaseCost])=True,0,[PurchaseCost]) AS PurchCost, IIf(IsNull([AverageCost])=True,0,[AverageCost]) AS AvgCost, SalesLine.SalesLineRate, SalesLine.SalesLineQuantity, IIf([SalesLineQuantity]<>0,[SalesLineAmount]-([SalesLineQuantity]*[PurchCost]),[LineAmount]) AS GPPurchCost, IIf([SalesLine.Type]="CreditMemo",([SalesLineAmount]-([SalesLineQuantity]*[PurchCost]))*-1,[GPPurchCost]) AS GPPurchCM, IIf([SalesLine.Type]="CreditMemo",([SalesLineAmount]-([SalesLineQuantity]*[AvgCost]))*-1,[GPAvgCost]) AS GPAvgCM, IIf([LineAmount]<>0,[GPPurchCM]/[LineAmount],1) AS [GP%], IIf([SalesLineQuantity]<>0,[LineAmount]-([SalesLineQuantity]*[AvgCost]),[LineAmount]) AS GPAvgCost, IIf([SalesLine.Type]="CreditMemo",[SaleslineAmount]*-1,[SalesLineAmount]) AS LineAmount, SalesLine.Type, Item.CustomFieldProductCode 
FROM Item INNER JOIN SalesLine ON Item.FullName = SalesLine.SalesLineItemRefFullName
WHERE CustomFieldProductCode = 'ABC123' AND  TxnDate Between #1/1/2019# And #10/1/2019#

Change the CustomFieldProductCode and the 2 Dates to valid selections.
-- When you execute this saved query, are results returned? Are the results correct?