hello,
I have two columns Column A and Column B. Both column has either a numeric values or blank.
I am trying to sum both columns Column A and Column B, but unable to get values in Sum Column C where either Column A or Column B has blank values.
Can anyone suggest what I should do?
([ColumnA]+[ColumnB])
I am creating a query which uses 2 unbound text boxes to populate a Between function for 2 date fields. If I fill in the dates, it returns the corresponding data. If I leave them blank, however, it returns no records. Is there an easy way to tweak the query to return all records if left blank?
View 4 Replies View RelatedI created this function to manage a MsgBox containing all required fields with no data:
Code:
Function FormValidation(TheForm As Form) As Boolean
Dim boolResponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolResponse = True
strError = Null
[code]...
Then, I have a Form_BeforeUpdate event, where I wanna place the function, which contains also some VBA code to manage duplicates records:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, dbs As DAO.Database, strICAO As String
Dim ctl As Control, txtMsg As String
[code]....
Now, how to add the function in this event to get these two results:
1. if required fields are blank and I press OK on the MsgBox, the routine must stop;
2. the focus must go in the first required blank control.
I tried some options but I get different kind of malfunctions: no custom message for duplicate records but only the access default one, "go to next record" feature with tab key not working and so on.
I have two issues with the report generation from a query:
(i) Sometimes blank values appear in the report in the serial number column generated dynamically (setting the control source property =1 and running sum = overall), but when clicked inside the particular box, the correct serial number appears
(ii) When grouped on a field, the particular field value appears sometimes more than once in the report inside the same group.
How to get rid of this?
Hi,
Each client has either an entry with a correspondence number or a notes number. So, the table would look somewhat like this:
Client: 333, Correspondence: 1, Notes: Blank
Client: 333, Correspondence: 2, Notes: Blank
Client: 333, Correspondence: Blank, Notes: 3
Client: 333, Correspondence: Blank, Notes: 4
How would I make a report that will make it look like this
Client: 333, Correspondence: 1, Notes: 3
Client: 333, Correspondence: 2, Notes: 4
Thanks! Hope that was clear.
G
I'm trying to execute a query that will delete values in a certain column, but I'm getting no positive result.
Quote:
--- Table1 ---
Original:
col1 col2
-----------
text1 Stext1
[Code]...
I have one combo box bounded with two fields, Id and Description through a query.
I entered a new id in bonded table but for some reason I don't want to enter description now.
And if I delete the entered id then I will loss that unique id which is using for description, which I don't want.Now the problem is one blank space showing in my combo box.
So what criteria I should use in query criteria to avoid blank spaces?
After creating several combo boxes in my new forms, I noticed that there were blank values being displayed. Is there anyway of creating of creating a listing of unique values and not displaying any blank values for the user to select from?
View 1 Replies View RelatedI've got a database with a field PKGNum, that was typically a three digit number, that used to be the primary tracking number for each project. You could have the same package number for multiple customers. About two years ago they began to use what they now call a PMIS number which is a unique number assigned for each project. So I have a seperate field in my database to track that.
I use a query to select information from my database to generate labels for my files. What I want to be able to do is have the system understand that if the field PMIS is blank, for a given record, to give me the PKGNum instead, so that the correct color coded value will print out on the labels. What I've been doing is having folks doing the data entry put the package number into the PMIS field if it's an old file, essentially repeating the value, so that I can pull the report using just the PMIS field for the color coded.
However, I would prefer to not have them have to enter duplicate information. Is there a way to tell the system when it's running the query, if PMIS is blank temporarily fill in the field with the value from PKGNum?
Any and all assistance is greatly appreciated.
I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015
2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.
How can I check if a table contains blank fields/values.
If there are blank fields I want to replace them with 0.
I have a list of employees and sort criteria. for example
empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F
The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0
E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0
I can get it to return:
empID......Count
1234...........2
1236...........3
using Count and the criteria Where Criteria="T" but not returning zeros.
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code:
SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;
Why some contacts work and some don't ??
I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:
Private Sub Command40_Click()
Dim strCriteria As String
strCriteria = createCriteria("[Introductions].Town", "List78")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Ownership", "List52")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Company", "List54")
[Code] ....
This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
Hello People!
I am trying to create a new variable that will indicate whether a person has a family history of breast cancer (1=yes). The values for the 4 source variables are either the number 1 or are blank/missing ("no" was not captured). Any help here is greatly appreciated.
Option Compare Database
Option Explicit
Public Function fnBrCaFaHx(BrstAny, BrstMth, Brst1Sis, Brst2Sis As Byte) As Byte
' Determine if participant has a family history of breast cancer.
If BrstAny = 1 Or BrstMth = 1 Or Brst1Sis = 1 Or Brst2Sis = 1 Then
BrCaFaHx = 1
Else
Exit Function
End If
End Function
I've created an array that I created and declared as a Public array in my module. I created a function that populates the array so that I can use the values in another function. I've gotten the array to populate but when I go to use the values in the array in another function, the array appears at Empty. I seem to be stuck on declaring it properly or something so that it can be used by other functions.
Public arrWebIDs As String
Public Function FillArray()
I have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.
View 7 Replies View RelatedI have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."
The expression I've written in the "Field:" box in Query design view is
Expr 2: DateAdd('yyyy',1,[Last_Date])
When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.
A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).
The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.
The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.
Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.
Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.
Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between]
FROM [Main Table]
WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
Hi everyone,
I have a a table called [Email Database] with many fields
I need to create a query that replaces all Null values with blanks or ""
I'm not that familiar with SQL so any help would be appreciated!
I have two tables (Table 1, Table 2). Table 1 is blank and has the fields "Field1, Field2, and Field3." Table 2 has data and 10 different fields ("Field1, Field2", etc). I want to copy all data from the matching fields in Table 2 to the corresponding Table 1. I've tried an Update Query in design view. Did not join any records, and just a simple:
Field: Field 1
Table: Table 1
Update To: [Table2]![Field5]
This returns 0 records to update. I thought about a Make Table to override the entire Table, but that isn't working either.
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...