Hi
I have the date on my table as 01/02/2006, there are others like this, i'm wondering how i can search for the whole month, something like **/02/2006. i have tried that way and didn;t work.
Any ideas??
Thanks
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I have attached two example for your reference.
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
View 3 Replies View RelatedI want to be able to search my table with a query in which it instructs the user to enter a month and it returns all the values in te table for that month.
the problem is that the date values in the table are in the form 18/08/2006
Is it possible to do this sort of query
I'm using a query to look up report criteria by type, response etc using a query to search value in a field ex [Reply]=[Forms]![Form1]![Cmbreply] Or [Forms]![Form1]![Cmbreply] Is Null
I'm trying to work a month look up the problem is the date is set July 25,2005 and my field is searching for the month July not the specific date. I'm stuck on how to look for the month only and ignore the day and year - any ideas
Thank you
Hi All,
I was wondering how would you search all records by a given month. What criteria would you put in the date field?
I have several date type fields. What I need to do is run a query that shows the MONTH as specified in the parameter, regardless of the day and year.
any thoughts
Hi!
What expession should I use to set the current month as criteria in a simple query? IE in the criteria field of the query design view...
Thank you for your help
how to search records by month and year.
example i will have form that contain combobox that will list Jan-Dec.
and also unbound box for user to enter the year.
then, it will have search button. after the user click search button, it will show report of the selected month and year.
I need to Criteria between currently Month and Next Month. (we are in April because of this im giving this example : 01.04.2015-31.05.2015) .I tried this code but its not working :
Dateadd("M") between DateAdd("M"+1)
I'm trying to create a combo list box in a form that has all 12 months (January, February......etc,) listed in rows, and depending on the selection will bring up only that particular month within a range of dates from a table. I don't want it to look at the day or the year, but only the month.
Example: I select January from the drop down list in my combo box and my form will display all records with dates that are in January regardless of month or year.
I am trying to return a value in an expression (call it FundedPeriod): CurrentWeek, CurrentMonth, PreviousMonth, based on a date value in field [funded_date].
Here are the criteria I am using:
Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())
Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())
Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1
Based on example, I expanded upon that and came up with this:
Code:
Function FundedPeriod(FD)
' returns CurrentWeek or CurrentMonth or PreviousMonth or None based on FundedDate (FD) criteria
Dim ret As Boolean
ret=CurrentWeek
[Code] ....
Am I in the ballpark w/ this in order to return:
CurrentWeek or CurrentMonth or PreviousMonth
so in the query expression I think I would type FundedPeriodName: FundedPeriod([funded_date])
How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.
For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.
But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.
Is this even possible?
I have a big list of data, with a row of data for every day for each client.
I need a selection criteria that will provide show the last day of the every month (historic data) for each client.
I've had a go but I'm not making much progress.
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
View 11 Replies View RelatedI am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedI basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View 2 Replies View RelatedI am trying to get deposit amount for the current month but results are in accurate i am using this
DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
Hi,
I have a search query (query by form) which is picking up keywords from a form and displaying matching results.
I want to add a tick box to the form, and if this tick box is ticked, I want the search to only include results which have a certain field NOT blank.
ie.
frmSearch will have tick box named "Website"
If "Website" is ticked on the form and the QBF run, the query will only show those records which have information in the field "Website Address" in the table tblPublication.
If the "Website" tick box it NOT ticked, the query will show all records, regardless of having information in the field "website address" or not.
can i do this in the criteria of the query?
I don't really want to do it by having a seperate query which is run by a seperate "Search" button on the form - this would be possible by having a 2nd search button (titled "Search for results with website") run a different query which has the critera that the field "Website Address" is not null.
I dont really want to have a seperate button and query as it makes it a bit messy - would rather the one query look up if the tick box, and if "ticked" then display only those with content in "website address" field, and if not ticked, display all regardless of content of field "website address".
any ideas?!
Cheers
amx
I have one table with 4 fields
TYPE
CODE
REASON DESCRIPTION
SHORT DESR
When I try to do a query to search on CODE it returns nothing. I don't understand what I'm doing wrong. Would someone please look at this DB and help> Thanks
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
Hi guys,
Cant seem to work this one out. I have quite a complex search form. The underlying query displays the results in a list box on the same form.
So far I have used the following expression for all the fields on my form (whether text or integer values):
Like "*" & [Forms]![Frm_FrmSearch]![AssetName] & "*"
This appeared to work correctly. However, now my Asset Management System is storing a number of Equipment Type's. As one of the query criteria is Equipment Type ID it means that selecting PC (1) also displays the details for Printer (11), Scanner (12) etc......
I know why it does this (because these numbers start with a 1 and I am using a like expression). However I cannot seem to get it working.
The equipment type value is present in every record so I dont think I can use =FormValue or FormValue Is Null. I did try:
=[Forms]![Frm_FrmSearch]![EquipmentType] Or
Like "*" & [Forms]![Frm_FrmSearch]![EquipmentType] & "*"
but it seemed to skip the first parameter and still displayed printers etc. as before.
Any ideas?
Hi All,
I need to make a search criteria within the same field,
for example here 'Demo' should selected from 'xxx' to 'xxx' number.
Thanks for reading, any suggestion would be appreciated
good day :-)
Sorry if this is an easy question, but I've been racking my brain trying to get this one.
I have a reference table of sales agents and assigned territories. Ex -
Agent Territory
Tom Smith IN, MI, TN, AR
Deb Jones IA, KS, NE, MO
Now I want to assign agents to a list of prospects based on their state id. However, I can't just join the state field from the prospects table to this reference table. How can I get this to work? Any help is greatly appreciated.