Im trying to add fields together in a query (these fields are results churned out by an iif expression), however access just concatenates the fields instead of summing. Access will not allow me to change the format of the original fields either.
Any ideas?
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
I am currently exploring the possibility of adding another element to our Database.
Each of our courses has a scheme of work associated with it, and this is completed on a daily basis by the tutor for each student. These are currently just Word documents we print out.
Example : I already have provided provision for recording attendance in the database, see below, (it may look a bit strange, but our training is ad-hoc so slightly different requirements than a college for example).
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate
The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
Thanks for the help.
In a query I have placed functions that work fine. Now I needed to round a currency number to the nearest $10. ex. 224.49 would be 220.00. I used round(xxxxxxx,-2). this gives me an error. positive 2 works fine. What's the deal? thanks for anyone who can help me. :)
View 3 Replies View RelatedA few years ago, I wrote an application in Access 2000 that worked fine. Last year I upgraded to Access 2003. Everything works the same, but the UCase, Left and Right text functions give error messages when used in queries. Anyone have a way to fix this?::confused:
View 2 Replies View Relatedhi
i have a DB which tracks order numbers,
the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail
i want to open a form and only show records from trackermaindata that contain my useremail -
SELECT dbo_TrackerMainData.userEmail
FROM dbo_TrackerMainData, userDetails
WHERE dbo_TrackerMainData.userEmail=userDetails.useremai l;
when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail
i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.
can anyone give any reason why this should not work? it seems straight forward enough!!
thanks for any help
Hi
I have set up a form to run a query, setting the following VBA to pick up values from the combo boxes in the form. The only problem is that I cant seem to get this working. When i press the command button it won't even get past the Dim db As Database line.
Private Sub Command10_Click()
On Error GoTo Errorhandler
Dim db As Database
Dim qd As QueryDef
Dim vWhere As Variant
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete "qry_email2"
On Error GoTo 0
vWhere = Null
vWhere = vWhere & " AND [Status]=" + Me.cbostatustype
vWhere = vWhere & " AND [Substatus]=" + Me.cboSubstatus
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub1
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub2
vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub3
If Nz(vWhere, "") = "" Then
MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
"Search Cancelled.", vbInformation, "Search Canceled."
Else
Set qd = db.CreateQueryDef("qry_email2", "SELECT * FROM tblgeneralcontactdetails WHERE " & _
Mid(vWhere, 6))
db.Close
Set db = Nothing
DoCmd.OpenQuery "qry_email2", acViewNormal, acReadOnly
Me.Command10.Requery
End Sub
help! I am now proper stuck with this.
Thanks in advance
Shapman
I have a query that created using the query builder.
THis is the SQL
But this wont work when using it in the VBA Code Line
I know this is a syntax issue...
Any thoughts?
Code:UPDATE dbo_Core2 SET dbo_Core2.STORAGE_LO = "DESTROYED", dbo_Core2.[DATE] = forms!frm_Main!Combo0, dbo_Core2.DISPO = forms!frm_Main!Text3WHERE (((dbo_Core2.STORAGE_LO)=[forms]![frm_Main]![Combo7]));
I'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had
[Forms]![MAIN]![Text440]
refering to the unbound text box on the form already mentioned.
This worked fine, but the following:
Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as:
Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
I am using Access2000...
If I copy paste the SQL from a query (View->SQL) to a macro it doesn't work.
Why?
I've tried removing quotes,Caps, adding quotes, parenthesis nothing worked.
Any ideas?
I have a Query pulling data from several tables. If there are empty cell with no data in them, they cause the Query to completely skip that record. It does not show up in the Query. Not practical since most of the data int the table will not be entered until some time later.
What gives? Is there a way around this?
Thanks.
Hi,
I am trying to get DATEADD to work in a query where the parts are called by different colums on the query like so:
Code:ExpiryDate: DateAdd("" & [DDType] & "",[DDNum],[Date])
At the moment the above will return the [DATE] without adding on the numbers
The [DDNum] is the number and works fine
The [Date] is the date and works fine
I know this becase the following code will give a new date
Code:ExpiryDate: DateAdd("d",[DDNum],[Date])
Could someone kindly let me know what I am missing in my syntax to get this working - I can get it working in ASP but I also need it to work in an Access Database
thank you for your time
I want to use Recordset to work with query but get the error "Run-time error '3061' too less parameters. Expected one".
Code:
Dim rst As Recordset
Dim strSQL As String
Dim krojenie As String
strSQL = "SELECT * FROM powtorzeniaDoKrojenia"
[Code] .....
I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View 11 Replies View RelatedGoal: : To improve an application's performance by basing sub forms and combo
The application has a front and back-end. There is no SQL server involved. I use a module for, seeking out the back-end and automatically linking to the back-end in order that I might be able to provide updates. This has worked for me since first setting everything up.
First let me define what I mean by, “sub form” as to how I am designing my application. I have a number of, what I call, “main (unbound) forms” that are used to open up separate application modules. Each module contains a number of hyperlinks to each of the other application modules. Also, each main form (application module) contains a tab form with bound forms. Therefore, I am calling these lower level bound forms, sub forms and basing them on query files instead of tables. Many of the tables have fields for collecting information across a second form. I did not want to bring back all the fields when they are used in any of the two are three forms bound with the same table.
Problem: When I base either a main form or a sub-form on a query, that particular front-end query form no longer links to the back-end database table via a query file whenever I import everything g into a new/fresh version of the front-end application. Whnever I open one of the query files via the form, I get an error message in each of the fields indicating it can't fine the field even though the table is listed with all the fields. No, I do not have the same problem with forms based on tables.
I suspect that my problem is in what I am calling a main and sub form; and that the main form is really the first level bound form and never the top unbound form if one is used. I have a lot of forms. I just wanted to get some feedback on
my suspicions or find out what else could be the source of my problem before re-doing everything.
Thanks,
John
The user currently manual checks the Use Part? box but would I would like to automate with an update query. I have this query that needs to be updatable but has a group by and min to get the correct results. I plan to run this query by another update query to update the table, but its currently not updatable. Is there a workaround or does anyone have another scheme to go at it. Here is my current queries.
SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], Min([t_BOM Part Cross].[Alt Unit 1]) AS [MinOfAlt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?]
HAVING ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND ((Min([t_BOM Part Cross].[Alt Unit 1]))>0))
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #];
I have another query that I used a SUB SELECT ON THE [Alt Unit 1] but does not group the other fields to get the results but it is updatable.
SELECT [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
WHERE ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND (([t_BOM Part Cross].[Alt Unit 1])>(SELECT MIN([Alt Unit 1])>0
FROM [t_BOM Part Cross])))
ORDER BY [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #];
thanks for any help available
I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.
i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);
There is one record missing... which is 31/12/2006 record.
So any idea to eliminate this??
Hi
I'm trying to finish a query that calculates the time worked in the AM and PM, adds them together, then takes that away from the standard working day.
I've done everything else, but when I take the hours worked away from the standard day it always ends up with a positive number, regardless if they haven't worked enough hours.
I've looked through the forums and everyone is talking modules, but thats over my head.
Thanks for looking, apprieciate the help.
Alan
With the imense help of some of the people in this forum, I have scraped together a nice little database.
There is one function, however, which is not working.
I have a query that should run from a form I have created. The form has multiple criteria and all seem to be working except for the date fields... "First Order" and "Last Order".
The way I have it set up is that I have a table of dates. The form has a list box of the dates (first of the month, for 4 years).
The source data in the form reads: SELECT [TDateSelect].[StartDates] FROM TDateSelect ORDER BY [TDateSelect].[StartDates]; (for each of the 2 "minimum" dates)
and SELECT [TDateSelect].[EndDates] FROM TDateSelect ORDER BY [TDateSelect].[EndDates]; (for each of the 2 "max" dates)
The user is allowed to select a date range for the first order and the last order. (4 dates altogether).
The form looks fine.
In the query, I have set the criteria for the last date as:
Between [Forms]![FSetOtherCriteria].[MinLast] And [Forms]![FSetOtherCriteria].[MaxLast]
When I run the query (even with a wide date range) I get no results. When I remove the dates from the query, or enter the limits manually, the query works like a dream.
Any ideas???
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA
I have a
Order Table:
Order ID(Autonumber)
Client ID(Text)
Client Name(Short Text)
Install Gross Rate (Currency)
Install Discount Given in % (Number)
Install Discount Given in GBP (Currency)
Then I designed a Query to calculate the Install Net Rate
Query
I selected
Client ID
Client Name
Order ID
Expression: [Install Gross Rate]*(1-[Install Discount Given in %]/100)-[Install Discount Given in GBP]
I run the query, but only ONE order calculated correctly, rest of Orders returned blank row.
When I run this query
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;
I get valid results.
When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.
I am running MS Access 2010 on a Window 7 pc.
Hi,
I am using a update query to update a table from another table. This query however will works only if I set the allowzerolength to yes in the table design otherwise validation error message will appear. This means that I cannot use the "Is Null " anymore because this empty space " " can appear. Is there any a way i can filter out those empty fields in the query design then? Thank you for your help.
I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.
Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."
So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:
tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"
The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.
When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.
As for my cascading combos, here are the settingsMatterTypeIDRow Source:
SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery
[Code] ....
I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter
data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?
Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.
I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.
DoCmd.SetWarnings False
Dim stDocName As String
stDocName = "Tupdate1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings Ture
If included the two setwarning statements, the update query won't work but when running without them, the update query worked well.