Query Help

[rkrause]

I have a qurey that has the field name like this:
PricingStart: DateAdd("d",-3,[MarketPrices].[MarketDate])
And in my criteria i want to do <=[Date] but that dont work. When its run nothing comes out in my results. When i hard code the criteria with a date it works fine. Any help would be great.

 

[MarkK]

In SQL I expect the date function should not be enclosed in square brackets and may need to have the parenthesis at the end ...

...DateAdd("d",-3,[MarketPrices].[MarketDate]) < Date()...

 

[rkrause]

This is in access. so in my criteria i should have <= Date()?

 

[MarkK]

I don't know anything about your criteria, but if you want to use the Date() function in SQL I think you must remove the square brackets and you might need to add the () at the end.

 

[rkrause]

Well Im working on an access qurey. And im trying to figure out how to get my results to work. MY criteria is wrong, as you can see from my original post and thats what im trying to get help on. I never said anything about SQL, and im on an Access forum in the qurey section.

 

[MarkK]

All queries in Access are written in Structured Query Language, or SQL. Access provides a design grid to simplfy the process of writing queries, which sometimes obscures the fact that you are actually writing code in a different language.
If you are indeed trying to use the Date() function in a query--and you have not stated that you are--then you must remove the square brackets and you may need to append the parenthesis. If you are instead trying to reference a field called [date] in a table that your query references, then you may need to prefix the fieldname with the tablename, like "[yourtable].[yourfield]".
Hope this helps,

 

[rkrause]

When im using is <=[Date] I want that to pop up a input box so that i can type in a date. thats the reason for the brackets, it can really be anything it could be <=[MinnesotaVikings] but what im trying to do is use this field:
PricingStart: DateAdd("d",-3,[MarketPrices].[MarketDate])

and in the criteria have it prompt me to input a a date. but its not wokring when i have it <=[Whatever], but when i put in a date like <=#12/10/10# it works.

 

[MarkK]

Do you realize that this is the first time you've mentioned that you are trying to elicit user input into the query? That seems like a really important piece of information.
Is there a field called 'date' in any of the tables you've referenced in your query? If so, the SQL parser will assume you mean that field value. If this is the case try using something like "[Please Enter Date]" as your parameter prompt. This will be more meaningful to your users and less likely to be confused with an existing field.
Does that solve it? Keep me posted.

 

[rkrause]

Sorry that i missed that key piece of info. i used this:
<=[Please Enter a Date]

and i still get no results.

 

[MarkK]

What does it mean that you get no results? Where and how do you use the query? It is a select query that returns records or is it an action query that modifies table structure or data?
Can you post the entire query text?

 

[rkrause]

Below is the query. I should get 1 result thats what i get when i hard code a date in. when i put this <=[Please Enter a Date] i get no results, hardcoded date and the date i put in the input box are the same.



SELECT MarketPrices.MarketDate, MarketPrices.BlockPrice, FinalPriceVariables.YieldPremium, FinalPriceVariables.CurrentMakeAdjustment, FinalPriceVariables.MoistureAdjustment, FinalPriceVariables.Qualification, FinalPriceVariables.[2001PriceConcession], FinalPriceVariables.IngredientAdjNov2010, FinalPriceVariables.KosherAdjustment, FinalPriceVariables.ProbioticUpcharge, FinalPriceVariables.CoreCoolingPayback, FinalPriceVariables.rbstMilkPremium, FinalPriceVariables.SegregationUpcharge, FinalPriceVariables.DDCFee, FinalRFPremium.RFPremium, DateAdd("d",3,[MarketPrices].[MarketDate]) AS PricingEnd, [Cheese Specs].[Cheese Type]
FROM [Cheese Specs] INNER JOIN (FinalRFPremium INNER JOIN (MarketPrices INNER JOIN FinalPriceVariables ON MarketPrices.MarketDate = FinalPriceVariables.MarketDate) ON FinalRFPremium.MarketDate = MarketPrices.MarketDate) ON [Cheese Specs].PG_KF = FinalPriceVariables.PriceGroup
WHERE (((DateAdd("d",-3,[MarketPrices].[MarketDate]))<=[Please Enter a Date]) AND (([Cheese Specs].[Cheese Type])="ncsr-c"));

 

[MarkK]

What does it mean that you get no results? Does the Enter Parameter Value dialog pop up? Do you enter a value there? Does the query execute without errors and return no records? Is that no result?

 

[rkrause]

yes box pops up, i enter in a valid date, and it executes with no results.

 

[MarkK]

Well, we're getting close. Can you test it by entering the date in the format dd-mmm-yy where today would be 17-dec-10, and don't use any delimiters.

 

[Tigs81]

i think if you change the where statement to add cdate it wil lwork

WHERE (((DateAdd("d",-3,[MarketPrices].[MarketDate]))<=cdate([Please Enter a Date])) AND (([Cheese Specs].[Cheese Type])="ncsr-c"));