Parameter Query with wildcard

Hi,
I have this parameter query Like "*" & [Date] & "*"

I would like to be able to add a date range. Unfortunately I can't use the standard Between - And parameter because my data is from a linked table that has the date and time in the field.

Thanks,
Ward
draw58Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Try this:


SELECT [Sales History].TKT_NO, [Sales History].TKT_DT, [Sales History].SLS_REP, [Sales History].STR_ID, [Sales History].ITEM_NO, [Sales History].DESCR, [Sales History].CATEG_COD, [Sales History].QTY_SOLD, [Sales History].EXT_COST, [Sales History].EXT_PRC, [Sales History].PRC_OVRD_REAS, [Sales History].NAM, [Sales History].CUST_NO, [Sales History].EMAIL_ADRS_1, [Sales History].NAM1, [Sales History].ITEM_VEND_NO, [Sales History].SUBCAT_COD, [Sales History].PRC_1, [EXT_PRC]-[EXT_COST] AS PROFIT, ([EXT_PRC]-[EXT_COST])/[EXT_PRC] AS [Profit Percent]
FROM [Sales History]
WHERE DateValue(nz([TKT_DT], #1/1/1900#)) BETWEEN CDate([Start Date]) AND CDate([End Date])

Open in new window

0
 
NorieVBA ExpertCommented:
Ward

What dates are you actually looking for?
0
 
mbizupCommented:
If you need to do a Date range comparison (user inputs the range), without the time portion, then switch to SQL View and use this for the WHERE clause of your query:

WHERE DatePart([YourDateTimeFIeld]) BETWEEN [Start Date] AND [End Date]
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dale FyeCommented:
personally, i would recommend using

"where [datefield] >= #" & [from date] & "# and [date field] < #" & dateadd("d", 1, [thru date]) & "#"
0
 
draw58Author Commented:
This is my SQL. I am getting an error "Wrong number of arguments used with function in query expression.

SELECT [Sales History].TKT_NO, [Sales History].TKT_DT, [Sales History].SLS_REP, [Sales History].STR_ID, [Sales History].ITEM_NO, [Sales History].DESCR, [Sales History].CATEG_COD, [Sales History].QTY_SOLD, [Sales History].EXT_COST, [Sales History].EXT_PRC, [Sales History].PRC_OVRD_REAS, [Sales History].NAM, [Sales History].CUST_NO, [Sales History].EMAIL_ADRS_1, [Sales History].NAM1, [Sales History].ITEM_VEND_NO, [Sales History].SUBCAT_COD, [Sales History].PRC_1, [EXT_PRC]-[EXT_COST] AS PROFIT, ([EXT_PRC]-[EXT_COST])/[EXT_PRC] AS [Profit Percent]
FROM [Sales History]
WHERE DatePart([TKT_DT]) BETWEEN [Start Date] AND [End Date]
0
 
peter57rCommented:
The function should be DateValue() not DatePart()
0
 
draw58Author Commented:
I get "data type mismatch" when i switch to DateValue
0
 
mbizupCommented:
Do you have nulls in your data?

Try

DateValue(nz(yourdatefield, #1/1/1900#))
0
 
draw58Author Commented:
Nope, I don't have any nulls. Where do I put DateValue(nz(yourdatefield, #1/1/1900#)) ?
0
 
draw58Author Commented:
Perfect! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.