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
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????
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