I have an error database that I'm attempting to build. I need the results of the query to show User ID when there are no errors during a defined time frame.
View RepliesI have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.
Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]
[URL]
As you can see, the PRODUCT_ID column is not combining together according to their IDs.
[URL]
This how my Design View looks.
The SQL for my current query is:
SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
[code]...
I have a line of code in an old program:
Dim CN as ADODB Connection
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
Hello all, im new here so hi to everyone.
Im stuck! I am pretty much a novice when it comes to access and have been given the task or creating some reports to stop us doing it manually.
Within a query, I have a date column which we would currently enter the following into the criteria:
Between #01/01/2008# And #31/01/2008#
for the month of January.
What i want to be able to do (and i know is possible as ive done it in the past but cant remember how) is have a pop up when the query is ran to enter the Start date and End date. I remember it being something to do with square brackets but cant get it to work.
Help please! lol
I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.
One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:
Code:
SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc
FROM tbl_OSRImport
WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));
This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:
In the Query, the expression is: VarPerc: [OverShort]/[NetSales]
When the Query runs, the full numerical result is: -4.27103159497526E-02
Which visually is output as: -4.72%
Mathmatically (on a calculator using the same values) the equation is as follows:
-11.22 / 262.70 = -0.04271032
I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.
Query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).
This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).
Is there a way to retrieve the value of a user-defined type in a query?
Here's the type:
Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type
I have a function with the following excerpt:
Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function
In my query I expected to be able to put the following:
Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount
However, Access didn't like the period in .Rank or .ClassCount.
Should I just write two different function to get 'rank' and 'classcount'?
Hi,
On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.
How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?
Thank you in advanced programming wizards. Kind regards, Adam.
Good Morning
Looking for the user to select a product name from a drop down box (not type in) when running query / report which will return details on only that product rather than all others in the query.
I have got major problems with the custom dialog boxes -- have got a table of product names, a query called current stock ( active products) . I can easily get a standard dialog box by putting [product code] in brackets in the query and this is not a problem. However in getting a custom dialog box I have had lots of problems.
Created a form called form999 which has product name field as a combo box and this has been named master1 . Clicking the form on it's own brings up the dialog box correctly and the drop down box selects the current products which is should do.
However linking it to the query I have put [Forms]![form999]![master1] in the query and it will not link unfortunately and is driving me mad.
Upon running the query it simply brings up the standard dialog box with [Forms]![form999]![master1] as the "enter parameter value. Understand code is required to get the OK and cancel macros working etc but when I click the query it should bring up the custom box which is the first stage and it is not ?? Any assistance would be greatly received.
Are there any sample DBs which have a field or related parameter as a customer dialog box -- have looked for examples without success.
many thanks and regards
Ian Watson Yarm UK
I am making a classic sales over time crosstab query.
Rows: Customers
Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013
September 8/21/2013 to 9/20/2013
Is it possible to format the columns this way?
Dim XL As Excel.Application
When I try to run this specific line of code an error occurs. It says:
"User-defined type not defined"
May I know how to solve this problem?
Thanks a lot
I've put in this in a module many times and this is the first time I have gotten an error.
Dim Conn As ADODB.Connection
When I run the app I am getting a compile error saying "User-defined type not defined".
Anyone have any ideas?
Thanks,
B
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 guys,
need your help again with the following problem i'm having. I'll give an example to make the problem clear.
I have a table, eg table 1 with the following records:
Tag________Desc________Msgno____DateTime
016GB001___Alarm OFF___1403______21/4/06 11:02:02
016GB001___Alarm OFF___1605______21/4/06 11:02:02
034GB005___Alarm ON____1403______21/4/06 11:02:04
016GB001___Alarm ON____1403______21/4/06 11:02:07
016GB001___Alarm ON____1605______21/4/06 11:02:07
048GB001___Alarm OFF___1403______21/4/06 11:05:31
048GB001___Alarm OFF___1605______21/4/06 11:05:32
048GB001___Alarm ON____1403______21/4/06 11:06:51
048GB001___Alarm ON____1605______21/4/06 11:06:52
I need a query that looks at records with similar Tag fields that occur with a 1 second difference between them and deletes those with "1403" in the Msgno field. I'm a newbie and i've got no idea where to start. The query should return the following records:
I have a table, eg table 1 with the following records:
Tag________Desc________Msgno____DateTime
016GB001___Alarm OFF___1605______21/4/06 11:02:02
034GB005___Alarm ON____1403______21/4/06 11:02:04
016GB001___Alarm ON____1605______21/4/06 11:02:07
048GB001___Alarm OFF___1605______21/4/06 11:05:32
048GB001___Alarm ON____1605______21/4/06 11:06:52
I hope the problem is clear. I know this is going to be one heck of a challenge, and i'm not sure whether this can actually be done in access!! Anyhelp from anybody would be enormously appreciated. Hope to see a solution soon!!
Kind regards,
Monty
I have a database being used to track and bill therapy units. I have a table that stores the pre-authorizations that tells me within a [start date] and [end date] I am limited with X amount of units (that is all the client's insurance and/or funding source will pay for within the time period). I also have about 4 different codes I can be authorized for which is also stored in this table - each code a separate pre-auth record.
Another table is where I enter in the type of therapy (service code) I've completed with the client. I put in the Service date, start time and end time and it will automatically give me the amount of units to bill (1 hour=1 unit). If I happen to choose a code or enter in a service date that does not match up with a client's pre-auth (Service date is between start date and end date and matches code from pre-auth table) I have a simple text box that says "No auth for this code or service date" due to an IIF statement in a query. I'd like to take this a step further and give me the remaining units for the pre-authorization left when it does find a match.
What is happening is when it finds the authorization, it's not taking into consideration all the services I've done for that time frame. For example: I have a pre-auth from 12/1/12 to 1/31/13 for code 90806 for 12 units. I served the client for that code a total of 10 units thus far. I can't figure out how to link my 4 service records dated 12/3/12 (2 units), 12/16/12 (4 units), 12/27/12 (2 units), and 1/3/13 (2 units) to add together and then subtract from my auth for 12 units. I know the dates are the key but I'm lost.
I am needing a fairly simple query to show records from a table that occurred between a set of dates specified by the user; I have quickly built the query using the wizard, however the date is stored as DateTime;
In the criteria, I tried - Between [Enter Start Date:] And [Enter End Date:] which returns an empty result (presumably because there are no records that match exactly, as they all have the time included?)
Is there a way to have the query show all results between the dates specified by the user, regardless of the time?
Has anyone ever produced a function to let users, pick their own tab order for a form.
I am thinking along the lines of holding a tab order for each user for each form.
So when a form opens - if a user has saved a tab order the form will adopt it, if the user has never saved a tab order, the sytem default will be applied.
Would this work.
Paul
Can anyone help?
Is it possible to have an autonumber that is generated depending on the entry in a particular field? I'm creating a database to track various enquiries and I would like each enquiry to have a unique number that is preceded by a codenumber that identifies its origin. For example:
ABC query - 100xxx
DEF query - 200xxx
XYZ query - 300xxx
(xxx denotes the autonumber part)
This way a particular query can be identified just from its number without having to interrogate the main record.
Can it be done? I've searched other posts and not found quite what I'm looking for.
(If not then no probs - I'll think of another way!)
Thanks in advance
I have a combo box that the user can select a value and the form will display that record on the form. This all works fine, but if the user dosn't enter anything in the combo box, it does not work. Is there a way to have it work if the combo box is left empty?
Thanks in advance - John
I am trying to get the list of all user defined tables from the Access database.
If I use the following query in Access it's working fine and getting the result. But if I am trying to execute the query in ASP page, it's not working. I am getting an error ([Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'.)
Can u please tell me the reason.
SELECT MSysObjects.Name AS TableName
FROM MSysObjects
WHERE (((MSysObjects.Type)=1)
AND ((MSysObjects.Flags)=0));
Note: If you have anything, to extract user defined tables, please tell me. Either one works out for me.
HI,
I have a query [CustomerOrdersByDate] that requests user input for [CustomerID], a 'Start Date' and an 'End Date' for the [OrderDate] field.
the Report works fine, however I'd like it to be able to take the Start & End dates the user has inputted when running the report, and make it appear in the Report header
e.g. Orders Received from [CustomerID], Between {user defined}[Enter Start Date] And {user defined}[Enter End Date].
I have added a text box to display the results and played around with a few different expressions, etc... unsuccessfully so far, and as I'm not up to working with VBA code, I would appreciate some help!
Hello,
Is there any way of creating a user defined query/ report based on fields in a table? Basically we want a report to be generated based on whatever fields the user enters. So, say we have 30 different fields in a table, the user gets to click on a button that gives them the option to create a report that includes: field1, field2, field3 and where field10 = Blue.
Is this possible to do?
I want to run an activity query (i.e. objective is to check which data lines have changed) within a user defined date range. Please advise what is the most efficient way to achieve this.
Is it possible to run such a report without having a date column i.e. does Access have a hidden line modification date that can be used in a search?
Look forward to your feedback - thanks!