with criteria: >=#01/10/2006# And <=#31/10/2006#
This seems to work on october data no problem
I had hoped to put all the september data into the same data table and filter but it failed so I created a new table called september data and pointed the query to it but for september:
with criteria: >=#01/09/2006# And <=#30/09/2006#
however the query fails if I take out the calldate expression it works no problem.
In fact it works providing I dont try and filter on date or sort by ascending.
it says data type mismatch in criteria expression. I had hoped that datevalue would turn the string into a date.
Its strange that it works in one query and not in the other with only the data being different.
Can anyone see why this should fail have I done something stupid?
I am a beginner with aggregate functions. I have two tables, one describing parts, and one describing assemblies that use those parts:
tblParts show all the part descriptions, including fldPartNumber.
tlbWhereUsed shows zero or more fldAssemblyNumber records for each fldPartNumber.
I want a query that shows, for each record of tblParts, the number of assemblies that use that part. I specifically want to do this with an aggregate function:
I am using Access 2003 with Windows XP Pro. I have a problem with using a query and the dreaded UK Postcodes!
I have a Client table that contains a field “Client Postcode 1”, in which is entered the first part of the UK postcode i.e. AB11, WC1A, E1, EC2V, etc.
In a Candidate table here are many text fields where we enter which postcode a Candidate wants to work in, i.e. TN, CV4, EC, W, etc.
We have a separate Candidate Search form where we select a particular client (Combo box from the Client table) which then displays the Client’s Postcode 1 data i.e. the first part of the UK postcode before the space.
We have a select query that then tries to match the Client Postcode 1 with the postcode that the Candidate wants to work in from the Candidate’s table.
In this query design grid I have successfully created criteria that will match the Client Postcode 1 field to the Candidate’s postcode field – very straightforward. This, for example, will match a Client with a TN39 postcode with a Candidate who wants to work in the TN39 postcode.
In the same query design grid I have also been able to successfully match the Client Postcode 1 field to a Candidate’s Postcode field using just the first 2 letters (using Left). This matches a Candidate who wants to work anywhere in the TN postcode area (TN1, TN2 etc.) with a Client whose postcode starts with TN
However, what is stumping me is where a Client’s Post code has only 1 letter followed by numbers (E1, B2 etc) in the first part of their postcode without returning EX, BL etc. I would like the accumulated wisdom on this forum to point me in the right direction to design criteria to input into the query design grid that will match ONLY the first letter of the postcode IF the second character is a number.
I have a query that runs to check if a form has any empty delay hrs.
Code: SELECT [Tble-wcDelays].ID1, [Tble-wcDelays].LinkingID, [Tble-wcDelays].HoursDelay, [Tble-wcDelays].ReasonDelay FROM [Tble-wcDelays] GROUP BY [Tble-wcDelays].ID1, [Tble-wcDelays].LinkingID, [Tble-wcDelays].HoursDelay, [Tble-wcDelays].ReasonDelay HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-RPC]![wetcleanID]) AND (([Tble-wcDelays].HoursDelay) Is Null)) OR ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-RPG]![wetcleanID]) AND (([Tble-wcDelays].HoursDelay) Is Null));
I'm trying to avoid cerating multiple queries that dothe same thing. But if this qurey runs from the form "FRM-RPG" then It errors because it can't find "FRM-RPC" so why does it not over look that frm and go to the 2nd Frm ?
I would like to seek guidance again on my new project, I wanted to create a data entry form that the user can input records using my unbound label boxes, then upon hitting an add button it will go automatically on a subform below or a GRID so that the user can see all the records at a glance.
I have Grid X and Grid Y set at 24. Usually I see dots all over my forms.... which allows me to snap controls to the grid. Today I only see large boxes instead?
In my first steps working with Access I feel unable to find out how can I create a report that shows the data (records from a Clients Table - Name, Phone, e-mail fields-) in a "spreadsheet like" format with gridlines. Can anyone help me on this?
I have a orders sub form that was working great .Then all of asudden when i when to input information into the fields i realised the grid under variuos field names has disappered (Icant enter any info),surly this must be something i have accidentally done without realizing.
I am writing a database to track our (and others) wine collections. i'm wanting to track what bottle is where. so i can look up what i have, look up where i have put it and away i go to get it, simple right?
just to complicate things i want a system in place. where the end user and use forms to add/remove or modify wine racks.
assuming that all racks are rectangular i would like to be able to add a rack and call it rack 1. rack 1 has 4 shelves all able to hold 5 bottles. the database would then say that bottle X is in rack 1 shelf 1 position 1.
if possible there will aslo be a screen that is auto generated by the database showing a simple grid with grrn and red squares denoting if the position if full or empty. the ability to click on a position to see what is in there or to add a bottle there would be amasing but not fussed if that can't be done.
I have a search page and would like to display the search results in a grid/table format. User will be able to select a row to go to a detail screen. What is the best component type to use for this?
I am struggling with a slight form delay. When it opens on the OnOpen event, i am populating a grid. This takes about 3 to 5 seconds but, the it also takes the form the same time to open. What i need to do is get the form to open then populate the grid. Can i make the form open first then populate?
I created a report that only has gridlines around the outside border of the entire report (rather than around each record). I want the grid line style on the last/bottom record to be solid so it boxes in all the records. When I change the "Gridline Style Bottom" for the records, each record has a bottom gridline rather than an outside box around all the records.
Here is a project. I have a grid on a form. its width is 20cm wide and 15cm tall. in the grid are 6 groups of columns (all columns listed are from left to right).
Group 1 - 5cm wide - description Group 2 to 6 are 30cm wide and represent days. Each group is split into 8 columns which represent hours per working day.
I need to to draw or position a line over the grid depending on what day it is and time. for example, lets say its thursday at 14.30. ( the 8 hour columns run from 8am to 4pm ) it would need to look like this-
So I need to work out the position of of the line depending on the time of of the day in the week. The day can be determined by the column header, the time by the grid....
when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.
Code: oListBox.Selected(lngRow) = True
However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).
Code: Print oListBox.Selected(lngRow) ->True
Access ListBox Control Under VBA Control Behaving Wacko - YouTube.It seems that the more times i requery the listbox, the worse the problem gets.
Code: oListBox.Requery
This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse. Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.
i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms). this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.
However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?
I've attached a stripped down version of a small order database I'm working on.
A user would enter an order, the amount and the date the order is required by.
As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.
What I'm trying to build are two queries:
1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)
2. A totals query which shows the total order amounts by season
