Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.








Sponsored Links:

Related Messages:
5 Parameters But Not Getting The Correct Results When Values Are Nulls
I have a question I hope someone can help me
I have 5 parameter query that gets its value from 5 combo boxes from a form

The
following function is in the criteria grid for one of the fields:

Like NZ([forms]![searchform]![combo69],"*")

when I enter data for a combo box for one of the combo boxes then if there is a null value in another one of the parameters combo boxes then it does not show. so I tried this:

Like NZ([forms]![reports_switch_main]![combo70],"*") or is null



but then when i run the form and i put an value in one of the combo boxes if there is a null value in the results it shows also, i don't want that. My form has 10 columns in which some have data and some dont for all rows


is there any way to do this? help!

View Replies !   View Related
Crosstab Query And Nulls
In my Accounts table I have a field called Status. This field is populated by single letters. A=Active, P=Paid and so on. I have a status (R) that is occasionally used. It won't show up in my crosstab queries since we currently do not have an account that is in R status. How can I get my crosstab to show the R status as having 0?

View Replies !   View Related
Nulls In Crosstab Query
I've been struggling with a problem and I hope one of you can please help.

I created a crosstab query that displays group names on the vertical axis and dates on the horizontal axis. I use a Count for the values. I use a pop-up dialog to get parameters for the dates (between... and), which are organized as Quarters. The query works fine and I can insert it into a report, which also works fine... with one problem. The dates only work when the date parameters request a year (4 quarters), and only work when each quarter has at least one entry. (Only work = I get an error message when one or both of these conditions are violated.)

Sometimes, I want to look at less than a full year, but when I use parameters for three or fewer quarters, I get an error. Same thing if I request a quarter that doesn't have any data. I assume the solution is to convert Nulls to Zeros and have wrestled with both Nz function and IIf, but have not been able to convert the values. I get the correct values in the quary's dataset if they are numbers, and blanks if no data, but no zeros anywhere. I also don't know how to make my report flexible or dynamic, so I can limit the number of quarters if I only want to look at a specific quarter or quarters (less than four).

Any ideas? Thank you so much for your time, and, Happy Holidays!

Stu

View Replies !   View Related
Cross Tab Query Nulls
is there any way to make a cross tab query default to zero instead of nulls for unpopulated cells

i know i can Nz all the resultant fields, but it seems like treating the symptoms and not the cause...

:confused:

View Replies !   View Related
Append Query And Nulls
So, I've been doing doing reading on Append Queries and Nulls. I have the following SQL statement


INSERT INTO tblTestResults ( EnrollmentID, TestType, Result, CompletedDate, BoldFace )
SELECT tblEnrollment.EnrollmentID, tblTests.TestsID, [Forms]![frmHome]![subfrmTesting]![txtResult] AS Result, [Forms]![frmHome]![subfrmTesting]![txtCompletedDate] AS CompletedDate, tblOptionsBF.OptionsBFID
FROM tblEnrollment, tblOptionsBF, tblTests
WHERE (((tblEnrollment.EnrollmentID)=[Forms]![frmHome]![EnrollmentID]) AND ((tblTests.TestsID)=[Forms]![frmHome]![subfrmTesting]![cboSelectTest]) AND ((tblOptionsBF.OptionsBFID)=IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),0,[Forms]![frmHome]![subfrmTesting]![cboSelectBF])));


and I can't for the life of me figure out.


IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),"",[Forms]![frmHome]![subfrmTesting]![cboSelectBF

Basically what I'm trying to say is that if the cboSelectBf is Null, then leave it null for the the append query or at least leave it blank. If it isn't, use the value that cboSelectBF has according to the combo box selection.

Any help would be greatly appreciated.

View Replies !   View Related
Include Row Number In Query Output
along with data in each row, i want to include the record number in the ouput of a query. is it possible?

once i run the query and view the ouput, i can see the standard access database record counter at the bottom or if i select a row, i can see the row number at the bottom.

how can i include that counter in each row of output from the query?

View Replies !   View Related
Tried To Execute A Query That Does Not Include The Specified Expression 'LastName' As
This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:

THIS GIVES ME NO ERRORS:

Code: Original - Code SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;
When I make the following changes to the SELECT section, I get this error:

You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.

Code: Original - Code ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ...
Any insight is greatly appreciated.

View Replies !   View Related
Query Results Include Number List
Hi all. Firts time here.

Got a access database containg sports data. Need to display number list with query result exampl.

Name BA
John Doe .299
Jane Doe .230

Desired Result

NO NAme BA
1 John Doe .299
2. Jane Doe .230
Need help. Thanks

View Replies !   View Related
Error: You Tried To Execute A Query That Does Not Include The Specific Expression
I have a combobox iBrand on a form frmFoodSub with the following query used to get its values (based on the imput of another combobox, iProduct, linked to the field Product in the table Products.) This code was generated by query builder, not myself:

Code:SELECT Products.BrandFROM ProductsWHERE (((Products.Product)=[Me]![frmFoodSub].[form].[iProduct]))GROUP BY Products.BrandORDER BY Products.Product;

And I keep getting this error message when the combobox is in focus on my form:

You tried to execute a query that does not include the specific expression 'Products.Product' as part of the aggregated function.

I have no idea what started this error. It was all working fine and then all of a sudden the error started appearing and I can't remember making any changes to cause it.

View Replies !   View Related
Help Needed Please: Date Parameter In Query - Leaving Blank To Include All Records??
Hi

Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!

I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.

I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).

Hope that makes sense, please can someone do me an idiots guide?

Many thanks
Phil

View Replies !   View Related
Nulls
Hi there,

I have 4 fields in a table(Call_Freq, Call_Week, Call_Day, Call_Sequence)

I have to make a query that finds records with Null values in these fields.

If they are all null that is fine so I only want to return records with either one, two or three of the columns with Null values.

Any help would be appreciated.

View Replies !   View Related
Help On Nulls
Hey guys,

I'm doing a pretty basic query that

Selects about 5 fields
From Table1
Where

(value=0 AND name <> HOT)
OR
(value=Null AND name <>HOT)

I also have some records (4 specifically) that have a name = nullvalue. When I run this query it picks out all the records with a name, and skips HOT(which is correct), but skips the nulls which I also want to include. Can someone help me in finding the reason for this error?

View Replies !   View Related
Cannot See Nulls
I already posted this problem in the query section, but got no where with it... here is the problem now...

I have a form with three combo boxs - cboStatus - cboFunder - cboResource on my form. Depending on what you chose as an option the subform, which is based on a query will show the data with the chosen criteria.

Only the Status field is a required field, so there are lots of nulls in the funders and the resource fields.

In the all the fields I have "ALL" or "*" as an option.

So in the criteria in the query I use this...

Like fCboSearch (forms!form name!combobox)

This calls up this function...

Public Function fCboSearch(vCboSearch As Variant)

If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If


End Function

This works great to show me all the records or just records with a certain criteria selected, BUT BUT BUT I cannot get it to show my the NULLS, they are no where to be found. How can I change this to show me NULLS??

Thanks. Joye

View Replies !   View Related
Update Nulls With Max+1
I have a table which records are appended to in batch format. I would like to update a column called batch number for newly appended records with the Max of that column + 1 but I keep getting errors such as "operation must be an updateable query". Anybody have any suggestions. Mind you I am not doing this in VB or SQL formats, I use the query design format. Thanks, Jim

View Replies !   View Related
Trouble With Nulls
hi,

i am having trouble with a criteria expression that seems to have an issue when it runs into null data in my table.

the query column is as below ...

DateSerial(Year([SomeDate]),Month([SomeDate]),1)

and the criteria for this column is ....

DateSerial(Year(Date()),Month(Date()),1)

So basically I am looking for those fields that match the first date of this current month.

for some reason when there are null values in the table this thing chucks back data type mismatch error but when i take the criteria away its all good

any ideas why this is happening?

View Replies !   View Related
Nulls And Zeros In Calculations
So, I have two tables that, cut down, look like this:

Table1:
Code budget
100 5
110 7
120 3
150 6

Table2:
Code actual
100 4
110 9
130 2
150 1

I have another table that is all the codes plus a description.

I thought I was being clever because I realised that there are items in Table1 that do not appear in Table2 and vice versa. I need a query that is, in effect, Table1 minus Table2.

I linked all three tables via “code”, created my query with the minus calculations and thought it had worked.

One problem. Where there is a “code” in one table but not in the other, the query puts a blank or “null” into that field. Then the minus calculation gives, say:

null - 2 = null

I’m used to Excel where:

blank - 2 = -2

Is there any way to get round this please? This must be a common problem, no?

Thank you.

View Replies !   View Related
Option Group Nulls Out
Will someone look at my option group in my form (frmcustomer) and help me figure out why the option group selections for existing records nulls out (visually you cannot see which button had been previously selected).

The option group is working fine in that it is adding the appropriate selection to the record, but when you open the form up in edit mode the selection has been grayed out.

View Replies !   View Related
Sum Of 2 Columns In SELECT Including NULLs
Hi!
I want to add two columns a and b. However, there are rows in which EITHER a OR b is NULL. This should be handled as if the cell would be zero. Is there any way besides filling in 0s in the tables?

I tried
SELECT a, b,
IIF( a = NULL, b, IIF(b = NULL, a, a+b))
AS c FROM

but this doesn't work.

Any ideas?

Cheers
Tom

View Replies !   View Related
Quick Question On Date Fields And Nulls
If I have a date field in a table that includes an actual date and I then reset the field programatically (i.e., [mydate]="") is the field now considered a null? My database performs date calculations but only on fields that are not nulls which is why I need to reset them at certain intervals.

View Replies !   View Related
Only Include Fields With Value 'YES'
Hi, first post here - may be a simple question, but it's been a bit of a while since I used Access, so my skills are getting rusty...

I have a form with a number of checkboxes writing to Yes/No fields in the underlying table. I then want the data to populate a report via a query. However, in the report, I would like to show only those fields where values equal TRUE/YES. In other words, for each record, only the fields where the user has checked the boxes should be displayed in the report. If possible, this should involve variable height for each record in the report, but this is not a must if too complex. It is, however, imperative not to show fields where the value is FALSE/NO. In other words, if all the fields has to go into each record in the visual layout of the report, is there a way to make the fields with negative values not visible? Any ideas how I can achieve this? I'm not sure if it can be done with just a simple Query, or with the SQL builder, or if it has to be done with VBA scripting (which would perhaps be applied to the report object rather than in the query?).

PS. If you know the answer, but are wondering how much you may need to dumb it down for my benefit, I have used Access a bit in the past, but not professionally. I have never bothered to learn VBA for Access, but have coded in VB and VBA for Excel, though, and know a bit of SQL and Java, which may give you a bit more of an idea what level to pitch it at :o).

Thanks for any input!

View Replies !   View Related
How To Include All Records Using WHERE
Hi everyone,

i am quite a begginer at this so sorry for the simple question

I am running a access query from a vba code. IN the vba code I am writing the sql command.

I am getting the criteria for the sql code (WHERE command) from a number of combo boxes in a form.
The whole thing works quite well when I have something selected on all combo boxes but it does collapse when one of the combo boxes has the default value which is "" nothing.

for instance when I have
WHERE domicile.IdDomicile = 5
works fine. 5 comes from the combo box

when there is nothing selected in my combo box the sql code would be
WHERE domicile.IdDomicile =

and then I get an error msg saying the query is wrong.

Question,

what is the command in sql that instruct the query to return all records.
I tried using "*" when the combo box was not selected but then I dont get the records that are blank, only the ones with something writen in it.

Any other suggestion for this

Many Thanks

K Regards

View Replies !   View Related
Does It Include [date]?
Hi All

Is there something I can write in to a query which will tell me whether the 1 April 2006 appears in between two dates (which are entered by a user?)

For example, if they put the first date (which is one field) as 25/3/06 and then they put the end date (which is another field) as 4/4/06 - I just want the query to produce the word "yes"?

Thanks

Maria

View Replies !   View Related
Include All Items Or Just By Group.
Here is what I have:

tblItemList:
IDItemList AutoNumber Field
ItemName Text Field

tblGroupingList
IDGroupingList AutoNumber Field
GroupName Text Field


tblItemDetail:
IDItemDetail AutoNumber Field
fkToItemList Number Field Joined to tblItemList
ItemVender LookupField
ItemavgCost Currency Field
ItemSRP Currency Field


tblItemGroupDetail:
IDItemGroupDetail
fkToItemList
fkToGroupList



Next I will add some tables to show orders. tblOrderList, tblOrderDetail ect

When I build the forms for the Orders I want to be able to show the list of items. Currently I have 16 Groups that Items can be in. An Item can have one or more groups or not be in a group at all. I would like to make the items selectable from drop down menus. But sorted by groups. So I would have a combo box of the group names. By setting it to "group one" the item combo box shows only items that are in "group one".

The part I am getting stuck on is how to "show all items" while not show the same item multiple times.
I am trying to do this in a query and can not seem to get it.

Any suggestion?

View Replies !   View Related
Include Pictures (jpg/gif) On Forms
I am putting together a query-based form for an auction, that needs to include an image of each item retrieved.

One of the columns in the source table contains the name of the picture file (e.g. "pic001.jpg","nopic.jpg") to be displayed for each row retrieved.

How do I link this text with the presentation of that image on my form?

I have a pretty sound knowledge of SQL and I'm fairly comfortable with Access.

All contributions welcome!

View Replies !   View Related
Not Include Codes That Have A Letter On The Right
I have a list of codes:

K101
K101A
K101B
K101C
K101D
K101E
L101
L101A
L101B
L101C
L101D

I want a query that displays all codes that dont have a letter on the right.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved