Hi!
When I run a querry I have a parametr querry like this:
Between[FirstDate] and [LastDate]
I want to "catch" the FirstDate and LastDate from the parameter querry and place them in the head of the Report based on the same querry.
How do I do this????
Hi!
I have a db in Access 2003.
I have a form named frmStat with FirstDate and LastDate and five options
I want to use the values in a querry.
I can catch the Datevalue with [Forms]![frmStat]![FirstDate] and so on but how do I do to catch the value from the options button?
Jack
I have a db with two tables linked by a Set_ID field. One table characterizes set information (date, time, location etc) and the other table has records for groups of fish caught in the set. The fish_table has (amongt others) a field for species (text), clip-status (yes/no), coded wire tag status (yes/no), and 'count' (number). The count field is necessary to allow input of groups of fish en-masse, or individually, depending on the amount of accessory information obtained.
Obviously, some sets catch no fish and so no record is entered into the fish table for those sets.
When I design a query, I want to produce a table that sums up the count field for each set, and produces subtotals for each species (and for the 4 variations of the clip/tag status fields).
My efforts so far are only partially succesful in that I can produce the correct subtotals, but only for sets where something was caught. Sets with no corresponding fish_table record are ignored instead of treated as zeroes.
Is it necessary to manually enter a 'zero' count for each species of interest for each set that we do? (Massively time consuming) Or is there some other way to query the db that forces the query to equate no fish record with a zero value?
Any thoughts? I've searched the forum, and googled, but haven't come across anything I could recognise as analogous to my dilema.
Hello all,
I am a buyer and I'm attempting to create a query in Access 2003 that will list all of my vendors (20 in total) and their top 10 selling sku's. Here are the fields I am using:
Buyer_Code
Supplier_Number
name
Sku_Global
OEM_Number
Part_Description_English
Status
C_Last12_SLS_QTY
I have created one Query including all of the above fields as well as the following field, "XP1: GetTen([Supplier_Number])"
In this query I have set "Supplier_Number" to sort ascending and "C_Last12_SLS_QTY" to sort descending.
I then created a module, written as follows:
Option Compare Database
Option Explicit
Public wSUPPLIER_NUMBER As String
Public wNum As Integer
Function GetTen(SUPPLIER_NUMBER) As Long
If wSUPPLIER_NUMBER = SUPPLIER_NUMBER Then
wNum = wNum + 1
Else
wNum = 1
End If
If wNum > 10 Then
GetTen = 0
Else
GetTen = wNum
End If
wSUPPLIER_NUMBER = SUPPLIER_NUMBER
End Function
I then created a second query with all of the above fields, and the criteria for the XP1 field as ">0"
As far as I can tell, this should bring me back a list with the first 10 rows for each of my Supplier numbers... unfortunately it is for some and for others it's bringing back more than 10 rows. The other problem is that it seems to be picking random rows to bring back, and not just the first 10 (which because in query1 I set the C_LAST12_SLS_QTY field to sort descending, should be my top 10 selling sku's).
Does anyone have any idea as to what I've done wrong? I'm pretty new with Access so I may be missing something pretty simple.
Any help would be greatly appreciated.
Antonio
Hey guys,
Anyone know how to go about catching the "Index or primary key cannot contain a Null value." message box and perhaps showing a custom message instead?
Thanks in advance,
Bob
I have come across this problem several times. I have sub reports on a report and calculations are done using the data in the sub report. In many instances the subreport is empty. The calculated fields read #error. (otherwise the report runs well and records with data calculate fine) I would like to hide these errors and associated labels when the subreport is empty. Everything I have tried either does nothing (error still appears in text box - but runs ok otherwise) or I get a runtime error since it is trying to access "nothing" in the sub report.
How can I catch and therefore react on this empty sub report. (or subform too)
Thanks
Lisa
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies View RelatedHi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
Hi all,
Can i populate a parameter just like i populate a combo box?
I want to have a FIXED value for parameter. May I know how to do this.
Thank you
Hi,
I am creating a database for work and I keep getting a message that pops up when I opn my form that says "tblClientBFinfo.ClosedReason". Then there is a place to enter a value and and it says "ENTER PARAMETER VALUE." I know it is a problem with one of my tables, specifically the "closed reason" field in the table. But I odn't know why it is asking me to enter a parameter value. Could anyone offer help?
Thanks!
I need a new parameter in my query. When I open the query i need the parameter to be asked in a combo box or drop down box or any way to select the value from the groupo other than typing.
View 1 Replies View RelatedI need a new parameter in my query which enables me to select from a field in the table rather than typing .
View 1 Replies View RelatedI want to run a query automatically in the gotfocus event of a field within a form.
Prior to the gotfocus, a value is entered in an unbound text box on the form and I want to use that value for the maximum value in the parameters. I will set the minimum to zero.
My code errors in the where, it has no problem with the serial number but I can't seem to set the deliv_no to pull the value entered on the form.
SELECT Sum(VOLTEST.unc_del) AS SumOfunc_del
FROM VOLTEST
WHERE (((VOLTEST.SERIAL_NO)=[Forms]![largeVolume1]![serial]) AND ((VOLTEST.DELIV_NO)>0 And (VOLTEST.DELIV_NO)<=([Forms![largeVolume1]![No_del])));
I figure this is a syntax problem. Can you guys see where I have gone wrong?
I need the value to write to the table, not the form when this runs.
Thanks, GG
In my chart I have a yes/no field that I would like to set up a query to for. I want the user to be able to input yes or no and then the data be displayed. Whenever I enter yes or no when the prompt comes up, I get an error saying the expression is typed incorrectly or the data is too complex. How do I set it up so the user can select yes or no in some way? thanks
View 5 Replies View RelatedHi all.I have a form that links to another form using the following macro "[homerid]=[Forms]![Homer Input]![homerid]" so when I click on form 2 the homerid from form 1 is automatically displayed. However on my switchboard I have a direct link to my Form 2 but when I click on this i get a box come up which is titled "enter parameter value" and then the subtitle is "[homerid]=[Forms]![Homer Input]![homerid]", this is fine, but I would like to change the subtitle to something i write myself such as "please enter homerid". Is their a way of doing this? Thanks.
View 2 Replies View RelatedHi,
I have been programing SQL for almost 3 years now but, am a newbie with access. I have just added a field to a form used by employees to enter time and am getting a parameter request when the user clicks Add Entry. I placed a msgBox before the insert line and noticed the field data (type memo) is being sent by the insert query as an integer (without quotes). Any ideas how I might get the insert to pass the Comments field as a string?
Thanks,
~Leo
Private Sub AddTimesheet()
On Error GoTo Err_AddTimesheet
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
'
Set db = CurrentDb()
'
strSQL = "SELECT * FROM tblTimeCard "
strSQL = strSQL & " WHERE [EmpID] = " & Me.cboEmp
strSQL = strSQL & " AND [Category] = " & Me.cboCategory
strSQL = strSQL & " AND [Week] = " & Me.cboWeek & ";"
'
Set rst = db.OpenRecordset( _
strSQL, dbOpenDynaset, dbReadOnly)
'
With rst
If Not .EOF Then
MsgBox ("Time sheet for this employee, category, and week already exists. Please click the record at the bottom of the screen to edit.")
Exit Sub
End If
End With
strSQL = "INSERT INTO tblTimeCard (EmpId, Category, Week, Hours, Comments, CreateDt, CreateID) SELECT "
If IsNull(Me.cboEmp) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.cboEmp) & ", "
End If
If IsNull(Me.cboCategory) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.cboCategory) & ", "
End If
If IsNull(Me.cboWeek) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.cboWeek) & ", "
End If
If IsNull(Me.txtHrs) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.txtHrs) & ", "
End If
If IsNull(Me.txtComments) Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & Trim(Me.txtComments) & ", "
End If
strSQL = strSQL & "'" & Now() & "', "
strSQL = strSQL & "'" & Trim(Me.txtLogon) & "' "
'Before Insert
MsgBox (strSQL)
DoCmd.RunSQL strSQL
'After Insert
Me.Refresh
Exit_AddTimesheet:
Exit Sub
Err_AddTimesheet:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_AddTimesheet
End Sub
Hello,
I do not know what I did but now when I open my database it asks me over and over again to "enter parameter value" and it won't let me open the database like normal. Please help!
Katie
Set Start & End Dates
I have a list box that lists the 52 week numbers of the year
week 1 being 26-03-2005 to 01-04-2005
week 2 being 02-04-2005 to 08-04-2005....etc.
My aim
By choosing a week no all the records within that date will be returned
I am trying to do away with having to type the start and end date each time a search is carried out
I need to be able to click on the week no and from that the results are shown. perhaps in another list box or something
Any thoughts on how to make this sort of thing work?
thanks
Hello,
I am designing a query as i have been advised in the following Quote:
Doc ManNow, you write a couple of queries. As a parameter to the queries, choose a date that will be your archiving cutoff or split point. Usually the last day of some month or quarter. Doesn't matter as long as you are consistent about it. The technical cutoff will be, in effect, 23:59:59 of the chosen date. The first record to be KEPT will be 00:00:00 of the next day in sequence.
Query #1 - Compute stock on hand as of your cutoff date. Create a single stock-on-hand transaction with the cutoff date. Put it in a temporary table until you need it. This will be a two-layer query. I.e. a query of a query.
Query #1A - a summation query grouped by item number for all transactions earlier than the archiving date so you can get the "inventory of X at archiving date"
Query #1B - an append query that draws from the summation query and feeds it to the temp table. The stock-on-hand record will have the archiving date
.
I am able to get "inventory of X at archiving date" and append the results to a temporary table but I can't see how to append the "archiving Date" data itself to the same table as the "Archiving date" is entered via parameter prompt? Any ideas?
Hi there
When using parameters from one form to the next I normally hide the form and then reference the parameter textboxes in the next form to the hidden form. Is there a better way of doing this as I saw threads here mentioning passing a parameter. How do I do this? Thanks!
Dave
How can you control SQL statement parameters with VBA? The SQL statement will not accept a variable.:confused:
View 1 Replies View RelatedI have a bunch of queries put in 1 macro. Of course, some queries have the same parameter [Enter current monthend] so when I run a macro, there are few popups asking to [Enter current monthend]. It’s annoying for users to put in the same parameter 2 or 3 times for a report. Is there a way to avoid it?
I tried to make [Enter current monthend] as a new_field so the 2nd query could pick up new_field instead of [Enter current monthend] again but that gave me an error b/c the new_field automatically has ‘binary’ datatype instead of ‘date’ datatype. How can I fix this?
Thanks.
ok, im new to access, so please bear with me!
i ma building a database for my boss, and everything was going somewhat smoothly until i got to parameters in my queries...
i have a date parameter in one query that reads, "enter date to view"
that worked fine, but now, whenever i make another query, even if it uses different parameters, when i run the query, the "enter date to view" appears, followed by the real paramater...
how do i correct this?!?!
thanks!
I have a form and it is related with a query and by clicking ok I got the required results in Access 97. But now I have converted to Access 2002 and whenever i enter values in the form and click ok I get a Dialog Box "Enter Dialog Box". I read MKB article but it did not help me at all. Please help me out I am not getting any idea at all to solve this problem?
View 6 Replies View Relatedwhen you create a query which requires a user-entered parameter, is it possible for parameter box to contain a combo box with a list of values?
View 1 Replies View RelatedHi All,
How can I check the Parameter query interval is out of range?
Details:
I have a table with Date column. With parameter query(by Date field) I extract records between two dates. How could I make a check when taping in Inputboxes, am I or Not Between the Last and the First Date?
Thanks!