It is basically a DCount, and it should find records, but returns 0 all the time. My code is:
Code:
Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 'Counts jobs kitted during shift given by sftd and sftn Dim timeformat As String timeformat = "#mm/dd/yyyy hh:nn:ss#" 'need this, to convert it to US datetime format cntkit = DCount("[JOB]", "Archive", "[Type] =" & typid & " And [Autfinish]=False And [SpecPaint] =" & specpaint & " And ([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")") End Function
sftstart and sftend are functions which are returning dates. The funcion works fine if I omit the Between part of the criteria. So the problem is in that part.
Here is the SQL that A2K gives me for the query that has the actions I want it to perform. But I can't get it to give me a count of the number of records returned. I would love to use DCOUNT but I am having a tough time working it out. Also in the final soultion, the numbers 2 and 23 are fields that I have 2 = weekday and 23 = [weekday] + [tt] which are in a different table. If someone could help me out with the DCOUNT formula, I would be forever appreciative.
SELECT DISTINCTROW noweekend.noweekday, noweekend.dayid FROM noweekend GROUP BY noweekend.noweekday, noweekend.dayid HAVING (((noweekend.noweekday)=1) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23)) OR (((noweekend.noweekday)=7) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23));
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on: 1) A Type field from my table 2) A calculated date based on other query fields.
My query has the following fields: [Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then if [Action 2] Is NotNull, Null if [Action 1] is NotNull, [Action 1] + 10 workdays if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.
here's the two methods i tried:
Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test
Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.
I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]
The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did.
I am using the dcount function as the example I display below. The problem is that it returns the correct result (i.e. 59) but the data type is text (59 is on the left side) . I need this to be number.
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code: Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.screenupdating = False excelApp.Visible = False Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
Lets say I have a form called form1 with 2 subforms named subform1 & subform2.
Can I place a text box on form1 that totals fields located on subform1 & subform2?
This is what I currently have as an expression in the text box. It is in the before update event, which may be the problem. Any ideas or solutions are much appreciated. :)
Ok, so I'm pretty new at this stuff, and while I'm sort of familiar with VB, I feel like there should be a fairly simple expression that could solve my problem. I have a calculated text box that is based on what is entered in another text box, like so:
the source number is in a field called Word Count. my calculated box's Control Source is =([Word Count]/250).
My goal is to automatically calculate the number of pages based on the number of words per page (250). I used Format so the bank rounding problem doesn't effect the outcome, but if the Word Count field is less than 125 the number of pages is 0. which is obviously not the case. I've tried fiddling around with the rounding, but then it starts rounding higher word counts up too soon.
Hello, I have been struggling to see on a new form data calculated on a different form. What I mean is that I have calculated a stock balance using a subform where the balance=[QuantityReceived]-NZ([Stock record table Subform].[Form]![SumQtyIssued])
Now, I woud need to see the balance value on an another form with the same Data source query. I have been trying to assign the control source of the balance as follow but doesn't work: balance=Forms![Stock record sheet]!Balance where [stock record sheet] is the form where the balance is originallly created.
I guess that you can't reuse data calculated in form and should probably store these data in a table or a query. Is there any other sensible way to do this?
Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)
I keep getting #name? as an error.
I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.
=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP FROM QryInout &_ WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_ GROUP BY QryInout.ProdType &_ HAVING (((QryInout.ProdType)="Freight"));)
Note that I have tried with and without the &_ and also with or with out the brackets..
I have a textbox in a report that I would like to calculate the sum of values that are less than 600.Then have a second textbox that counts the number of records that have values less than 600..This is based off of a query with calculated fields..I have been working with a formula that looks something like this
=Sum(IIf([BaleETime]="<600",0))
But all I get is a value of 0.BaleETime is the elapsed time between bales.
I have a textbox on a form that displays a calculated number with regards to input from other text boxes.
I need this calculated number to update to a corresponding field within a table.
I have a button that updates the information, but the calculated field will not update.
I have an "=" expression written that does all of the calculating. When I put this in the 'control source' it calculates and displays on the form perfectly, but will not update the table. If I change the control source to the field within the table and then move the "=" statement/expression someplace else....the field is blank on the form, but I can manually input a number and it will update to the table.
I have 4 texts fields [DEVICE INSTALL] , [INSTRUMENT STAND], [BRACKETS RACEWAY SUPPORTS], [TUBING CAPILLARY] all can be either a percentage from 1-100 or "N/A" except [DEVICE INSTALL].I would like to calculate the [percentage complete]
my calculation in the field is as follows: ([DEVICE INSTALL]+ IIf([INSTRUMENT STAND]="N/A",100,[INSTRUMENT STAND])+ IIf([TUBING CAPILLARY]="N/A",100,[TUBING CAPILLARY])+ IIf([BRACKETS RACEWAY SUPPORTS]="N/A",100,[BRACKETS RACEWAY SUPPORTS])) /4
this seems to work to a degree. but for some reason when the [INSTRUMENT STAND] value is not "N/A" the percentage calculated is a wacky value much higher than 100. how could I possibly change my division #?
I have 3 values in unbound text boxes and a 4th text box that is calculating the sum of the first three text boxes, i need the result of the calculation to be stored in the field "Total Cost" that is also on this form.
I'm creating a report to check for over- or under-stocked items. The report is working fine, gets all the records etc. except that it thinks that the OnHand field from my inventory query is text or something, at any rate not a number. I have successfully set the format of the field in the query to General Number, but that doesn't seem to have worked. Here's the SQL for the report:
Code: SELECT DISTINCT Signs.SignCode, Signs.SignDescr, Size2.XYdim, qryOnHand.OnHand, Bins.Rack, Bins.Level, Bins.BinNum FROM (Size2 INNER JOIN (Signs INNER JOIN (Items INNER JOIN qryOnHand ON Items.[ItemsID] = qryOnHand.[ItemsID]) ON Signs.[SignID] = Items.[SignID]) ON Size2.[SizeID] = Items.[SizeID]) INNER JOIN (Bins INNER JOIN InventoryDetail ON Bins.[BinID] = InventoryDetail.[BinID]) ON Items.[ItemsID] = InventoryDetail.[ItemsID] WHERE (((qryOnHand.OnHand)<=[Check for signs with fewer than:]));
I would like to append a text item to each value in a calculated field. consider the field name is "Division" and I want to append the word "Division" to the values put out by the field "Division"
My attempt was: Division & " " & "Division". This produced an error in the report.
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname] =Sum([Quarter1_A]) - Named "Quarter_Total" =Sum([Quarter1_T]) - Named "Quarter_Target" =Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
Was wondering if someone could possibly help me with a DCount problem i'm having.
I have a form with a subform, displaying bookings that customer has made. What i want to be able to do, is when a booking is created for a customer in this subform, after the time period chosen is selected, i want a DCount to run, go to a table of regular bookings, count up how many bookings in it have the date of booking, that same as the date just put into the subform, AND the time period of booking the same as just put into the subform. There can only be 1 result at max due to its setup, and from there it should be fine, but i cannot get it to work. The field names are as follow:
Subform: Date for Booking Time Period
tblRegularBookings Date For Time Period
If this doesn't make any sense i can try and explain better.
