Is the Date/Time Criteria Right? (1 Viewer)

[prabha_friend]

Between Format([Forms]![Bills]![Bill_Date],"dd-mm-yyyy") And DateAdd("s",-1,DateAdd("d",1,Format([Forms]![Bills]![Bill_Date],"dd-mm-yyyy")))

Query getting blank on using:
ORDER BY Max(Bills.Bill_Date) DESC;

But Desired record coming by on using:
ORDER BY Bills.Bill_Date DESC;

Please Help. Thank You.

With Hope,
Prabhakaran

 

[plog]

ORDER BY will not limit the results unless you are using TOP as well.

Can you post the full SQL? Actually, a copy of your database with the sample data would be best along with the value you are putting into your form.

 

[Minty]

No Idea why you are formatting the dates? Try just using

Between [Forms]![Bills]![Bill_Date] And DateAdd("s",-1,DateAdd("d",1,[Forms]![Bills]![Bill_Date]))

 

[prabha_friend]

Forms involved:

1. Bills
2. Sales (Sub Form)

Query on Progress:

1. Prices

 

[arnelgp]

you can also use for Criteria:

>=[Forms]![Bills]![Bill_Date] And < DateAdd("d",1,[Forms]![Bills]![Bill_Date])

 

[prabha_friend]

Any suggestions?

 

[plog]

You are using 4 criteria in your query, what 4 criteria am I to use to recreate your issue?

 

[prabha_friend]

Bill Record No: 718 of 754
Sale Item: Tomato Nadu (1st Record)

Giving No records on Prices (Query)

 

[plog]

Again, 4 criteria. Not to sound all Matrix-y on you but I'm not dealing with your forms, I'm dealing with code:

...(((Bills.Bill_Customer)=[Forms]![Bills]![Bill_Customer]) AND ((Max(Bills.Bill_Date)) Between [Forms]![Bills]![Bill_Date] And DateAdd("s",-1,DateAdd("d",1,[Forms]![Bills]![Bill_Date]))) AND ((Sales.Sales_Item)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item]) AND ((Sales.Sales_Item_Unit)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item_Unit]))

Every time you see a [Forms]![Bills]! in there I need what value to plug in to recreate your issue.

 

[prabha_friend]

Criteria 1: Bill_Customer = 9
Criteria 2: Bill_Date = 08-07-2022 06:07:39
Criteria 3: Sales_item = 34
Criteria 4: Sales_Item_Unit = 1

 

[plog]

There's no records that meet your criteria. This is because of the Bill_Date criteria--no record in your database occurs after 7/11/2022, so no records will be between 8/7/2022 and 8/8/2022 .

Additionally, your query becomes invalid and will not run if you simply change the ORDER BY to:

ORDER BY Bills.Bill_Date DESC;

And touch nothing else.

 

[CJ_London]

looks to me like you are using a UK style date of dd/mm/yyyy with your formatting. formatting creates a string and in Access sql you have to use the US format of mm/dd/yyyy or the sql standard of yyyy-mm-dd, plus you need to include the # delimiter which tells the query engine that what is between the is to be treated as a date.

Would really help if you were more specific as to where the problem is since I don't see

Between Format([Forms]![Bills]![Bill_Date],"dd-mm-yyyy") And DateAdd("s",-1,DateAdd("d",1,Format([Forms]![Bills]![Bill_Date],"dd-mm-yyyy")))

anywhere.

But to modify your code I would use Arnel's example, modified to

>='#'& format([Forms]![Bills]![Bill_Date],'yyyy-mm-dd') & "#' And < '#' & format(DateAdd('d',1,[Forms]![Bills]![Bill_Date]).'yyyy-mm-dd') & '#'

 

[Pat Hartman]

1. NEVER format a date when you can work with it as a date. Formatting is for people, NOT for computers. Internally, the date is NOT stored as a string. It is stored as a double precision number. The integer part is the number of days since Dec 30, 1899 and the decimal part the the elapsed time since midnight. Negative numbers are dates prior to Dec 30, 1899. As was pointed out, formatting a date turns it into a string. That will make it act like a string so 24/03/2020 is > 20/02/2022 and will sort after that date as well as comparing later.
2. If you are creating an SQL string rather than using a querydef, you will need to format your date BUT, you will need to use either the unambiguous #yyyy/mm/dd# or the US standard of #mm/dd/yyyy#. In your querydef, you are referring to a bound control. Because the control is bound and is known to be a date, the query engine will be working with a double precision number. It will NOT be working with a string so there is no ambiguity. When you format a date and convert it into a string #dd/mm/yyyy# and #mm/dd/yyyy# can be ambiguous so SQL Server ASSUMES #mm/dd/yyyy#. That is why you can't use the dd/mm/yyyy format, ever in a query. In addition, if you are ever using an unbound control with a date and referencing it from the QBE, add a date format to the format property of the control. This tells Access that you expect the control to contain a date. That way it stores the data as a double precision number rather than a string.
3. NEVER use Now() as the default value for a date unless you actually want a time component in the date field. Bill Date does NOT need a time component so you should be using Date() rather than Now() to eliminate time because that is also causing a problem for you. arnel explained how to get around it. He is really good at providing band aids. I suggest that you actually fix the problem. To fix the problem, change the default to Date(). Then run an update query that replaces the date+time with just date. Use the DateValue() function to do this.

 

[Pat Hartman]

@Prabhakaran you "like" everything but you don't use your words to tell us what solution you are actually using. Liking everything is not helpful to the people who find this post later. What is helpful, is you telling people which advice solved the problem when you can. If English is not your native language and so you have trouble writing in it, just reference the thread that solved the problem if there is one thread that did that. You can "like" the others if they were helpful but the experts here are adults and they don't need "likes" (except for a couple of them) to reinforce their competence.

 

[prabha_friend]

SELECT Bills.Bill_Customer, Max(Bills.Bill_Date) AS MaxOfBill_Date, Sales.Sales_Item, Sales.Sales_Item_Unit, Last(Sales.Sales_Price) AS LastOfSales_Price
FROM Bills INNER JOIN Sales ON Bills.Bill_ID = Sales.Bill
WHERE (((Bills.Bill_Date) Between [Forms]![Bills]![Bill_Date] And DateAdd("s",-1,DateAdd("d",1,[Forms]![Bills]![Bill_Date])) Or (Bills.Bill_Date) Is Null))
GROUP BY Bills.Bill_Customer, Sales.Sales_Item, Sales.Sales_Item_Unit
HAVING (((Bills.Bill_Customer)=[Forms]![Bills]![Bill_Customer]) AND ((Sales.Sales_Item)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item]) AND ((Sales.Sales_Item_Unit)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item_Unit]))
ORDER BY Max(Bills.Bill_Date) DESC;

The above change in the query solved the Problem.

 

[prabha_friend]

SELECT Bills.Bill_Customer, Max(Bills.Bill_Date) AS MaxOfBill_Date, Sales.Sales_Item, Sales.Sales_Item_Unit, Last(Sales.Sales_Price) AS LastOfSales_Price
FROM Bills INNER JOIN Sales ON Bills.Bill_ID = Sales.Bill
WHERE (((Bills.Bill_Date) Between [Forms]![Bills]![Bill_Date] And DateAdd("s",-1,DateAdd("d",1,[Forms]![Bills]![Bill_Date])) Or (Bills.Bill_Date) Is Null))
GROUP BY Bills.Bill_Customer, Sales.Sales_Item, Sales.Sales_Item_Unit
HAVING (((Bills.Bill_Customer)=[Forms]![Bills]![Bill_Customer]) AND ((Sales.Sales_Item)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item]) AND ((Sales.Sales_Item_Unit)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item_Unit]))
ORDER BY Max(Bills.Bill_Date) DESC;

The above change in the query solved the Problem.

Resolved on initial Tests but still have doubts over it... If someone reviews and provide the feedbacks. we can close it "solved"

 

[mike60smart]

Hi

This makes no sense at all:-

Criteria 1: Bill_Customer = 9 - I take this to mean the PK of the Customers Name? ie "Sathar's Restaurant"
Criteria 2: Bill_Date = 08-07-2022 06:07:39 - There is no matching date
Criteria 3: Sales_item = 34 - I take this to mean the PK of the Items? ie "34"
Criteria 4: Sales_Item_Unit = 1 - I take this to mean the PK of the Items ie "1"

What is the final result that you are expecting to see?

 

[Pat Hartman]

Why would you use the complicated Between which is trying to compensate for time when time does not belong when arnelgp gave you an alternative? Which are necessary as long as you are including time where it doesn't need to be. If you want to know when (time) a record was actually saved, add a separate field. Don't corrupt the order date with time.

 

[plog]

Resolved on initial Tests but still have doubts over it.

You've only thrown code at us, some that "worked", some that didn't. You never explained what you were trying to do nor what data you expected the query to return. We cannot help with your doubts because we don't understand the big picture.

All you did in the final query to change it from your initial query is allowed NULL values for Bills.BillDate to come through.

 

[prabha_friend]

The Goal:
If there is a repetition of order items in the same order then we have to sell on the same price the item entered the bill for the first time (Consistent item price across the bill for repeated items)