I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) ) However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
Everything works great but I want to save the backup file with a unique name, like EX: myfile INTx.xls where intx = the date "Now", maybe, am I looking at this wrong. Thanks Bob
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
How to implement a functionality into my access database. The related tables required for this functionality can be found as an attachment.
When the information has been entered into the tables by the user, I would like to calculate the difference between the information for each part within the same table. The main columns for which the difference have to be calculated are: [RefObsInfo->RefSources] and [AltObsInfo->StatusAltSource] and [GenObsInfo->GenSources]. The values for these fields are restricted to 3 or 4 different ones. I also would like to show if the GenEstAvailable columns are changed, but that's for later.
In order to calculate this difference a query has to select for each Part the information related to the last date AND a date which has been specified by the user (with the previous date as max). For this the user is asked to give a from date, which she/he can select from a combobox. This combobox has a standard selected value which is the MaxDate-1, i.e. the previous date. The difference should be calculated from the previous date to the last date. I would prefer to give each (predefined) difference a specific action to show the user the difference in a matter of seconds.
Hi All, I am modifying some asp product catalog query to sort by order of 'price' so the data from the DB is displayed on the page from cheapest to most expensive, I am a newbie but keen..... <%if not request.QueryString("cid")<>"" then%> </font> <%end if%> <% if request.QueryString("cid")<>"" then set rs = cn.execute("Select * From Products Where CategoryID= " & request.QueryString("cid")) end if %> <% if not rs.eof then while not rs.eof %>
This extract shows a request to select products from a specific category but I want to disply them in order of ascending price....can any power users out there set me straight. greatly appreciate it. cheers now
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.
Any pointers in the right direction would be greatly appreciated.
i have this query that i have several buttons that trigger it. how do i pass over a unique variable with each button. each button needs to append a different event. right now i have it set static to "3rd Party Denial" but i need that to be dynamic based on which button is pressed. PLEASE HELP!!!!!! Code:INSERT INTO [Status Log] ( Status, Edit_Date, Event, Claim_ID )SELECT [Status Lookup].Status, Now() AS Expr1, "3rd Party Denial" AS Expr3, [Claim Report Info].[Claims Header].Claim_IDFROM [Status Lookup] RIGHT JOIN ([Claim Report Info] LEFT JOIN [Status Log] ON [Claim Report Info].[Claims Header].Claim_ID = [Status Log].Claim_ID) ON [Status Lookup].status = [Status Log].StatusGROUP BY [Status Lookup].Status, Now(), "3rd Party Denial", [Claim Report Info].[Claims Header].Claim_IDHAVING ((([Status Lookup].Status)=[Forms]![claiminformation]![ReportForm]![reportstatus1]) AND (([Claim Report Info].[Claims Header].Claim_ID)=[Forms]![claiminformation]![ReportForm]![Report_ClaimID])); currently i have a macro running when clicked. that macro runs 2 quieres and a report. i'm doing this because thats the only way i know how.
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890 ABE12345 ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
I am needing to compare some information in a way I cant with the normal Unmatched Query.
I have 2 tables with the fields:
| PHONE1 | PHONE2 | FIRST | LAST |
What I need to do is have Table 2 check against Table 1 to see if any of the 'PHONE1' records match 'PHONE1' or 'PHONE2,' and if they do have them remove just the information from PHONE1, not the entire record.
Then I would have to repeat the process to do this with PHONE2. Having it check against 'PHONE1' and 'PHONE2' in Table1.
If both PHONE1 and PHONE2 have a match, the entire record would be deleted. If not, it would keep whichever did not match.
I am currently having to do this manually, because if I just run an Unmatched Query it gets rid of the whole record regardless of if PHONE2 is a good number or not.
If any more information is needed, let me know. Like I said, Im relatively new to working in Access and dont know much.
I have a table where I have 5 columns, (BatteryL1, BatteryL2, BatteryR1, BatteryR2 and BatterySize).I want to update BatterySize, with a value if any one of the above listed columns contains a specific value using something like 'LIKE' in the query.
For example: UPDATE Customers SET BatterySize=13 WHERE BatteryL1 LIKE '%13%'
Given a table field that is a hyperlink type. I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value. Any ideas how? Thanks.
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
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"
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address] FROM [UK Table] WHERE (([UK Table].[Business Name] Like Forms!frmNz!txtBusinessName & "*") Or (Forms!frmNz!txtBusinessName Is Null)) And (([UK Table].[Business Type] Like Forms!frmNz!txtBusinessType & "*") Or (Forms!frmNz!txtBusinessType Is Null)) And (([UK Table].Address Like Forms!frmNz!txtAddress & "*") Or (Forms!frmNz!txtAddress Is Null)) And (([UK Table].City Like Forms!frmNz!txtCity & "*") Or (Forms!frmNz!txtCity Is Null)) And (([UK Table].Country Like Forms!frmNz!txtCountry & "*") Or (Forms!frmNz!txtCountry Is Null)) And (([UK Table].[Telephone Number] Like Forms!frmNz!txtTelephoneNumber & "*") Or (Forms!frmNz!txtTelephoneNumber Is Null)) And (([UK Table].[Website Address] Like Forms!frmNz!txtWebsiteAddress & "*") Or (Forms!frmNz!txtWebsiteAddress Is Null)) And (([UK Table].[Email Address] Like Forms!frmNz!txtEmailAddress & "*") Or (Forms!frmNz!txtEmailAddress Is Null));
I have used this query but fail to know what the & "*" part means. What does it do? What does it mean?
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.