Query problem

[mikekal]

I have a query that accept this:
<=[frmRptDialogSingle]![enddate] and not this:
=[frmRptDialogSingle]![enddate] in criteria

 

[theDBguy]

Try taking out the "=". I think it's implied.

 

[mikekal]

II tryed but same problem

 

[theDBguy]

II tryed but same problem

Can you please post the complete SQL statement for your query? Also, are making sure the form is open when you run the query?

 

[mikekal]

SELECT [Inventory Transactions].ΑΑ_ΧΡΕΩΣΗΣ, [Inventory Transactions].[Transaction Item], [Inventory Transactions].Quantity, [Inventory Transactions].[Unit Cost], [Inventory Transactions].[Created Date], [Inventory Transactions].ΑΡΤΙΜΟΛΟΓΙΟΥ, [Inventory Transactions].ΠΟΣΟΛΟΓΡΑΦΟΣ, [Inventory Transactions].ΑΑ_ΠΙΣΤΩΣΗΣ, [Inventory Transactions].[Transaction Type], Inventory.Supplier
FROM Inventory INNER JOIN [Inventory Transactions] ON Inventory.ID = [Inventory Transactions].[Transaction Item]
WHERE ((([Inventory Transactions].[Transaction Type])=1) AND (([Inventory Transactions].[Created Date])=[frmRptDialogSingle]![enddate]))
GROUP BY [Inventory Transactions].ΑΑ_ΧΡΕΩΣΗΣ, [Inventory Transactions].[Transaction Item], [Inventory Transactions].Quantity, [Inventory Transactions].[Unit Cost], [Inventory Transactions].[Created Date], [Inventory Transactions].ΑΡΤΙΜΟΛΟΓΙΟΥ, [Inventory Transactions].ΠΟΣΟΛΟΓΡΑΦΟΣ, [Inventory Transactions].ΑΑ_ΠΙΣΤΩΣΗΣ, [Inventory Transactions].[Transaction Type];

 

[theDBguy]

SELECT [Inventory Transactions].ΑΑ_ΧΡΕΩΣΗΣ, [Inventory Transactions].[Transaction Item], [Inventory Transactions].Quantity, [Inventory Transactions].[Unit Cost], [Inventory Transactions].[Created Date], [Inventory Transactions].ΑΡΤΙΜΟΛΟΓΙΟΥ, [Inventory Transactions].ΠΟΣΟΛΟΓΡΑΦΟΣ, [Inventory Transactions].ΑΑ_ΠΙΣΤΩΣΗΣ, [Inventory Transactions].[Transaction Type], Inventory.Supplier
FROM Inventory INNER JOIN [Inventory Transactions] ON Inventory.ID = [Inventory Transactions].[Transaction Item]
WHERE ((([Inventory Transactions].[Transaction Type])=1) AND (([Inventory Transactions].[Created Date])=[frmRptDialogSingle]![enddate]))
GROUP BY [Inventory Transactions].ΑΑ_ΧΡΕΩΣΗΣ, [Inventory Transactions].[Transaction Item], [Inventory Transactions].Quantity, [Inventory Transactions].[Unit Cost], [Inventory Transactions].[Created Date], [Inventory Transactions].ΑΡΤΙΜΟΛΟΓΙΟΥ, [Inventory Transactions].ΠΟΣΟΛΟΓΡΑΦΟΣ, [Inventory Transactions].ΑΑ_ΠΙΣΤΩΣΗΣ, [Inventory Transactions].[Transaction Type];

Try adding this little change...

... AND (([Inventory Transactions].[Created Date])=CDate([frmRptDialogSingle]![enddate])))...

 

[mikekal]

Im sorry do nothing.The same problem

 

[theDBguy]

Im sorry do nothing.The same problem

No error message?

 

[mikekal]

No error.Just show nothing.But if i make it <= its ok.But i want records in specific day.Im sorry for bad english

 

[theDBguy]

No error.Just show nothing.But if i make it <= its ok.But i want records in specific day.Im sorry for bad english

Ah, that tells me you have time components in your data. Is that correct? If so, you'll have to either account for them or remove them.

 

[LarryE]

[frmRptDialogSingle]![enddate]
should be:
[Forms]![frmRptDialogSingle]![enddate]

 

[mikekal]

Im sorry nothing again

 

[mikekal]

Only accept < =

 

[theDBguy]

Only accept < =

Did you see my post? Can you verify if you have time values with your dates?

 

[Minty]

Change this
(([Inventory Transactions].[Created Date])=[frmRptDialogSingle]![enddate]))
to
((DateValue([Inventory Transactions].[Created Date])=[frmRptDialogSingle]![enddate]))

 

[mikekal]

I found that and working :Like "*" & [Forms]![frmRptDialogSingle]![enddate] & "*" Or Is Null

 

[SHANEMAC51]

У меня есть запрос, который принимает это:

SELECT IT.ΑΑ_ΧΡΕΩΣΗΣ,  IT.[Transaction Item],  IT.Quantity,
 IT.[Unit Cost],  IT.[Created Date],  IT.ΑΡΤΙΜΟΛΟΓΙΟΥ,
 IT.ΠΟΣΟΛΟΓΡΑΦΟΣ,  IT.ΑΑ_ΠΙΣΤΩΣΗΣ,  IT.[Transaction Type],
 Inventory.Supplier

FROM Inventory
 INNER JOIN [Inventory Transactions] IT
 ON Inventory.ID = IT.[Transaction Item]
WHERE (((IT.[Transaction Type])=1)
AND ((IT.[Created Date])= FORMAT([frmRptDialogSingle]![enddate],"\#MM\/DD\/YYYY\#)))

GROUP BY IT.ΑΑ_ΧΡΕΩΣΗΣ, IT.[Transaction Item], IT.Quantity,
 IT.[Unit Cost], IT.[Created Date], IT.ΑΡΤΙΜΟΛΟΓΙΟΥ,
 IT.ΠΟΣΟΛΟΓΡΑΦΟΣ, IT.ΑΑ_ΠΙΣΤΩΣΗΣ, IT.[Transaction Type];

 

[Pat Hartman]

I have a query that accept this:
<=[frmRptDialogSingle]![enddate] and not this:
=[frmRptDialogSingle]![enddate] in criteria

Are you getting an error? What is the error? Is the second option not returning the results you expect? I'm guessing that is the case. theDBguy already asked if you had time values in the date field and you never answered.

LIKE is a string operation. Dates are NOT strings. Therefore the expression that you think is the solution is very inefficient because it prevents Access from ever using an index and requires it to read every single row in the table to determine what to select. This is not a problem if you have only a few thousand rows but it will get slower over time.

I agree with theDBguy, you almost certainly have time in some of your date fields which is why <= works but not just plain =.

The solution is to use TimeValue as was suggested if you WANT the time to be part of the date. If you don't then the solution is two-part. First, you need to find the code/queries that use Now() when they should be using Date(). Then you need to run an update query (back up the database first) to replace the existing values with DateValue() to remove the time.

And finally, if you have a format property defined on the date fields in the table - REMOVE the format. All this is doing is causing a problem because it is obfuscating the data and causing confusion. The format does not remove the time, it just hides it so you can't figure out why the query isn't working.