Problem with query after adding WHERE Date Parameter (1 Viewer)

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
Hello Guys , I have a problem with Query Result in the attached database.
Short Version :
I need the query named Result to sum the QtyOut from QrySales Like the Following hypothetical Dsum
Code:
QryResult.[QtySold] = Dsum("[QtyOut]","QrySalesPlans","[Code]='"&[ProductCode]&'" And [Client]='"&[Client]&'" And [Zdate] Between #QryFollowUpPlans.[ClcFromDate]# And #QryFollowUpPlans.[ClcToDate]#

Which means i need query
Result to show all the records from QryPlans and only the records from QrySales where the joined fields are equal ("Code","Client") And Where the Zdate From QrySales Between ClcFromDate And ClcToDate (Which are mentioned besides PlanSerial in QryPlans).

Long Version :
I already tried to make it happen by using parameter in date :
SQL:
Between [ClcFromDate] And [ClcToDate] Or Is Null
and join query result with other queries and it works fine in some cases like if there are sales in the period between ClcFromDate To ClcToDate then it works fine and Sum as supposed but , If the sales date not in the period between ClcFromDate To ClcToDate then the query result hides the entire row due to the previous mentioned parameter.

Hope i could explain more , Thanks in Advance
 

Attachments

  • Amr.accdb
    508 KB · Views: 304
Last edited:

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
I made an example for the desired result , Thanks.
Capture.PNG
 

June7

AWF VIP
Local time
Today, 02:31
Joined
Mar 9, 2014
Messages
5,399
So you want item Code to show in Result even if there is no data for the period? Try joining Result to TblPlanDetails on Code fields.

Unfortunately, Client field will be empty for the Mouse record.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:31
Joined
Feb 28, 2001
Messages
26,946
Code:
Between #QryFollowUpPlans.[ClcFromDate]# And #QryFollowUpPlans.[ClcToDate]#

The syntax here is wrong in that you only use "#" (the octothorpe or pound sign or hashtag mark, pick your favorite name) for literal date strings or substituted date strings. Not for fields or controls that putatively ARE in internal date format already. Here is what the substitution would resemble.

Code:
"...Between #" & Format( QryFollowUpPlans.[ClcFromDate], "dd-MMM-yyyy" ) & "# And #" & Format( QryFollowUpPlans.[ClcToDate], "dd-MMM-yyyy" ) & "#"

If this is done in code behind a form and those controls are on the form, you can make QryFollowUpPlans.[ClcFromDate] into Me.[ClcFromDate], which means less typing.

EDIT: 2nd look .... that DSum also isn't terminated properly, missing a close parenthesis. That surely won't work correctly.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,872
Between [ClcFromDate] And [ClcToDate] Or Is Null
Syntax. "What" is null? You need to say specificlally

Between [ClcFromDate] And [ClcToDate] Or [ClcFromDate] Is Null
OR maybe:
Between [ClcFromDate] And [ClcToDate] Or Forms!yourform![ClcFromDate] Is Null
 

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
So you want item Code to show in Result even if there is no data for the period?
Yes , that’s the point , I tried every thing and the problem here with the date parameter , I need to figure it out . Thanks
 

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
Code:
Between #QryFollowUpPlans.[ClcFromDate]# And #QryFollowUpPlans.[ClcToDate]#

The syntax here is wrong in that you only use "#" (the octothorpe or pound sign or hashtag mark, pick your favorite name) for literal date strings or substituted date strings. Not for fields or controls that putatively ARE in internal date format already. Here is what the substitution would resemble.

Code:
"...Between #" & Format( QryFollowUpPlans.[ClcFromDate], "dd-MMM-yyyy" ) & "# And #" & Format( QryFollowUpPlans.[ClcToDate], "dd-MMM-yyyy" ) & "#"

If this is done in code behind a form and those controls are on the form, you can make QryFollowUpPlans.[ClcFromDate] into Me.[ClcFromDate], which means less typing.

EDIT: 2nd look .... that DSum also isn't terminated properly, missing a close parenthesis. That surely won't work correctly.
Thanks for your reply , This Dsum is hypothetical just for making my point ignore the syntax entirely it is just for clarifying the idea .
 

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
Syntax. "What" is null? You need to say specificlally

Between [ClcFromDate] And [ClcToDate] Or [ClcFromDate] Is Null
OR maybe:
Between [ClcFromDate] And [ClcToDate] Or Forms!yourform![ClcFromDate] Is Null
Thanks for your reply , If you took a look on the join relation between QryPlans and QrySales you will find >> show all the records from QryPlans and only the records from QrySales where the joined fields are equal ("Code","Client") And Where the Zdate From QrySales Between ClcFromDate And ClcToDate.

So the Is Null was for if the query didn’t find any Sales of the code in the specified period and it keep showing the code in QryResult , That was my fix for not showing all records from QryPlans , So my parameter is absolutely wrong . I need to fix it or use entire new way to get what i need
Between [ClcFromDate] And [ClcToDate] Or Forms!yourform![ClcFromDate] Is Null
There is no form , Just using queries , I need it to stay that way
 

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
Hello again , Some may wonder why i didn’t just mention PlanSerial in TblSales and join the QryPlans with QrySales on PlanSerial ? my answer is the sales report come from another department and have so many records and it will be exhausting to add PlanSerial Manually .
So i have new idea First to add PlanSerial Field in TblSales then make some function to do the following :-
Search for code and client -(Mentioned in QryPlans)- in QrySales in the period mentioned in QryPlans and Update the PlanSerial to the right one which match all conditions ?
But Obviously i don't have the skills to make this function 😅
Thanks guys.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:31
Joined
May 7, 2009
Messages
19,096
you can create a Query for that:
Code:
SELECT
    qryPlans.PlanSerial,
    qryPlans.Client,
    qryPlans.ClcFromDate,
    qryPlans.ClcToDate,
    qryPlans.Code,
    qryPlans.Product,
    qryPlans.Qty,
    Nz((select Sum(QtyOut) From qrySales AS T Where T.Code = QryPlans.[Code] And (T.zDate >= qryPlans.[ClcFromDate] And T.zDate <= qryPlans.[ClcToDate])),0) AS Out,
    [Qty]-Nz((select Sum(QtyOut) From qrySales AS T Where T.Code = QryPlans.[Code] And (T.zDate >= qryPlans.[ClcFromDate] And T.zDate <= qryPlans.[ClcToDate])),0) AS Remains
FROM qryPlans;
 

VBANewBie :)

Member
Local time
Today, 12:31
Joined
Apr 14, 2021
Messages
88
you can create a Query for that:
Code:
SELECT
    qryPlans.PlanSerial,
    qryPlans.Client,
    qryPlans.ClcFromDate,
    qryPlans.ClcToDate,
    qryPlans.Code,
    qryPlans.Product,
    qryPlans.Qty,
    Nz((select Sum(QtyOut) From qrySales AS T Where T.Code = QryPlans.[Code] And (T.zDate >= qryPlans.[ClcFromDate] And T.zDate <= qryPlans.[ClcToDate])),0) AS Out,
    [Qty]-Nz((select Sum(QtyOut) From qrySales AS T Where T.Code = QryPlans.[Code] And (T.zDate >= qryPlans.[ClcFromDate] And T.zDate <= qryPlans.[ClcToDate])),0) AS Remains
FROM qryPlans;
Exactly what i want just Perfect , Thanks you (y)(y)
 

Users who are viewing this thread

Top Bottom