I am having trouble with a datasheet its ran from a macro button using BrowseTo command.
I am having trouble with the where condition; I would like to show records where the [FittingDate] is blank...
I have tried isnull() and [FittingDate]=""
I am having trouble getting a query or report to show only the most recent data.
We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.
I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.
Here is my data table: Inventory Scans from stores.
Scan Date
Item Scanned
location
1/1/2014
item123
Store ABC
1/1/2014
item123
Store ABC
......
Here is my Query with Totals that counts the item records:
Scan Date
Item Scanned
location
(Item Scanned) count
1/1/2014
item123
Store ABC
2
1/1/2014
item 456
Store ABC
3
1/1/2014
item 789
Store ABC
1
2/1/2014
item123
Store ABC
2
2/1/2014
item 456
Store ABC
1
This is what I am trying to get - only the most recent date of counted items:
Scan Date
Item Scanned
location
(Item Scanned) count
2/1/2014
item123
Store ABC
2
2/1/2014
item 456
Store ABC
1
I m trying to make form which filters my records and generates a report..here's where I am
Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Code:
Like "*" & [Forms]![Form1]![Text6] & "*"
the code is in report record source
I want to filter my subform data, to only show records where field A is a higher value than field B.
Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True
This way it doesn't find field B.
Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True
This way it seems to filter all record to the field B value of the first record.
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
What should I do for solve my problem?
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
What should I do for solve my problem?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have created a DB to store my students records, eg: Student Number, English Name, Chinese Name, Birthdate, Class Number, Age, and exam scores, I also have a photo frame to display the students photo.
In the data sheet I have an ole object field where I add the photos, the thing is it means I need to manually add this to the data sheet, whereas I can add all the other details directly from the form "Add New Record" and also search, delete or edit all from the form.
I have searched and searched but I cannot find any info that accurately explains how I may add the photos to the c where I have placed a button "Add Photo". I assume I need to create a macro and assign it to the button (on click) then this would need to open a search file location box where I can find the photo and select it to be added to the file.
The point is I wish to be able to do all the adding, deleting and editing from within the form and not have to enter into the data sheet at all.
I am using Office 2010
I created a new field as a text box, converted it to Combo box, then the Values are pulled from a query and all that works fine.
I have a form that opens and displays these Fields in a Data Sheet view and the new Combo box doesn't drop down and is flagged as a text box in the property bar but no way to change it??
I need the drop down like the other Category field I have, that works but this one doesn't.
See screen shot.
You can see in the Category field, there is a Drop down, but in Category II there is not, even though this is a drop down field. - BUT in this data sheet view it doesn't show it as a drop down.
[URL] ......
I want a very basic report that shows:* all field reps (sales people),
* their quota in terms of new customer registrations
* their actual new customer registrations for a particular month
The report should include field reps with 0 sales. The query I've created works fine as long as there's no date filter. But adding <1/1/2013 removed all field reps with 0 sales. I want to make sure the filter concept was working before getting into date ranges.
Code:
SELECT DISTINCTROW tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota, Count(tbl_customers.dateRegistration) AS NewRegistrations
FROM tbl_ksFieldRep LEFT JOIN tbl_customers ON tbl_ksFieldRep.[salesRepName] = tbl_customers.[ksFieldRep]
WHERE (((tbl_customers.dateRegistration)<#1/1/2013#))
GROUP BY tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota;
Can someone help?
I need the code for a filter or query which will show the past x (12, 24 etc) months worth of data. Each record has a date tag so i need a filter which will just show records for say the past 3 months.
Any ideas?
I have a form that has numerous fields, there is one field that is called the "Relationship Code" it gives any customers who have a certain relationship a 3 digit number. I would like a command button that you can press that will take the current record shown and filter their relationship code, to only show those records who have the same code. I know this can be done by going to the field>right clicking>and saying Equals " ". But I want a button with code that will do this automatically.
View 1 Replies View RelatedI have a navigation form with a combo box. The combo box has a running list of manifest numbers. I select a manifest number, then click a button that brings up a new form with data related to the selected manifest.
However, I have to click and unclick the 'Toggle Filter' button on the new form to show the correct data each time I open the form.
I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.
View 5 Replies View RelatedIn access, I have the following: 2 sub forms located in an overall form. I would like when I select a record in the first subform, that the second subform would automatically filter for all records that contain the same ID Value.
View 1 Replies View RelatedDear All, I have a field named: “reminder date” in a query and would like to show only records where the reminder date is within the next week whenever the query is run (ie if todays date is 26th may 2006 and I run the query now It would only show records that include the following dates 27,28,29,30,31, july and 1,2 June)
Ive tried the below bit of code but this shows to many records beyond one week.
>=DateAdd("d",+7,Date())
Anybody have any suggestions?
Regards
Jim
I have txt input [txt choice] which has a number (1-9) and then a query which uses the following formula to define which records are visible linked to a Yes/No field
Code:
=IIf([Forms]![frm selection]![txt choice]="4",Yes,Yes or No)
If the choice is "4" then it works fine, but if the choice is anything but 4 it still shows only the Yes items. If i change the code to
Code:
=IIf([Forms]![frm selection]![txt choice]="4",Yes,No)
Then it does show just the 'No' items but i want it to show 'everything' if the choice is not 4
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I am trying to set a date filter that filters between two dates (Start/End) after I have selected filters from other combo boxes. This is what I Have so far and is a bit of a mess.
I have two text boxes - txtStartDate and txtEndDate
If Nz(Me.txtStartDate.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
ElseIf Me.cboCity.ListIndex <> -1 Then
Me.Form.Filter = Me.Form.Filter = "NextCallDate
[Code] .....
So the idea is I will filter all records by my name then by status then by city and then by start date and end date.
:confused:Hi,
I'm quite new to Access, and would like help with a query.
I need to show all records with the date field 14 days before todays date.
If i havn't explained this clearly enough then i'll have another go.
Thanks
Dan
I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information
On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly
Then, on another form someone else can check what 'Jobs' should be done on that day
I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)
The problem is that i have been limited to the options of 'repeatability'
What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"
Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?
I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.
I have a continuous form bound to a query. I want to filter this form with any date inputted into a text box through a pop-up calendar control. The default date in the text box should be Date().In the query, I set the criteria on Call_Date filled as follows but the form does not requery to return the expected data:
Call_Date:
Criteria: Forms!F_On_Call_Officers!txtEndDate
One the face of it it sounds quite simple - i have a table that holds various bits of info about a customer but also a field that contains a holiday period - what i want to be able to do is show all records that do not equal todays date.
So if today was 11/04/06 - and one of my customers had 11/04/06
as one of his holiday days he would not appear on the report but all the others would.
Any help would be greatly appreciated
regards
I have a table with the following 5 fields. (Service Type), (Valve Name),(Size),(Rating),(Description).
I want to do two thing:
First: I want to select the required information from the first 4 fields using combo boxes and get the last field (description) based on the selected 4 fields. In other words, i want the record to be filtered using first 4 fields to give me the last field info.
Second: I want to store the filtered record (all 5 fields) in another table.
I need a calendar that can go forever that shows weeks and not months and each record i add (or is added by admin form) will show up. I have a calendar attached here to show what i mean but its monthly not weekly.
Basically it needs 7 spots for employee vacation leave, 2 spots for supervisor leave and 2 spots for other leave. My current calendar will only fit the 7 employees and no1 else thus the reason i want to go weekly and not monthly, so it has more room.
Ok I have a list box (CounselorInitials) and a listbox (AssignedToYou)
The list box has 6 columns and is using a query (AssignedToMe). The important one is the 6th one (Counselor). All working off a table called DityLog.
I want to select initials in the combo box and then it will filter the listbox and only show records that are the same.
So if I select initials MC from the combo box I want the Listbox to show all records that have the initals with MC.