Need To Display Value From Another Table If Current Value Is Null

Jul 13, 2006

There are 3 main tables

tblDrawingRegister - stores information about construction drawings
tblDrawing Revisions - each drawing in tblDrawingRegister has one or more revision or version
tblVendorDrawingList - this table has a list of drawings numbers that correspond to the drawings in tblDrawingRegister.

The drawing numbers from the vendor table as provided as an 'extra' as some people are more familiar with this numbering system.

Now the issue is that there is a one-to-many relationship between tblDrawingRegister and tblVendorDrawingList. In the screenshot I've attached there are 14 drawing numbers that correspond to one drawing number in tblDrawingRegister. (This is because the main supplier has packaged 14 drawings as one with just one drawing number). When this is the case I want to display the drawing title from the Vendor table. Otherwise if should just show the title from tblDrawingRegister.

I have tried using Dlookup to show the title from the vendor table when there are dublicates in the vendor table. It didn't work and I think this would be really slow. I've tried a few other things too but to no avail.

I really hope that this is clear. I've attached screenshots of the query too.

View Replies


ADVERTISEMENT

Display All When NULL

Aug 3, 2006

I have a query which based on some fields on a form should display a set of records. In case when the user doesn't specify anything in the combobox on the form (value=NULL) I want the query to display all the records (like "*"). For some reason when i put the following in the criteria of the query it returns 0 records instead of displaying all:

IIf(IsNull([Forms]![frmClassReport].[ClassName]),(([tblClassesOffered].[Course]) Like "*"),[Forms]![frmClassReport].[ClassName])

I tried: IIf(IsNull([Forms]![frmClassReport].[ClassName]),"class_name",[Forms]![frmClassReport].[ClassName])
... and that works properly. I tried several scenarios and pretty much narrowed the problem down to the use of "LIKE" which when used as specified in the first SQL statement doesn't return any records. What am I doing wrong?

View 1 Replies View Related

Display Date Even If Null

Jan 19, 2005

Good day,

I have a query that returns hours of vacation time taken for each specfic day of the year. But if no time is taken on for example Jan. 19th, that date do not show up in the query. I am wondering is it possible to return all 365 days and display a zero,if nothing is booked for that day. Attached is the db.

Thanks

Chris

View 4 Replies View Related

Queries :: Set A Null Value To Display (0)

Aug 24, 2014

I need the following to display a zero if a null value is returned:

SELECT tdsIndivData.dsReportID, Count(tdsIndivData.StaffID) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;

View 5 Replies View Related

How To Display Current .mdb Filename?

Sep 8, 2004

I have a replicated database. I'd like to display the name of the version of the database that is being used on the data entry form so it's clear which version is being used.

What's the code for the current filename? (I'm talking about the entire "filename.mdb" file).

View 4 Replies View Related

Display A Null Result In A Query

Jan 7, 2007

Hi,

I'm trying to work out a formula in a query.

At the moment it looks like this:

Days on Hold: calcworkdays([on hold date],[off hold date])-1

Now i have a module thingy set up (calcworkdays) which works out working days. What this expression does, when theres an on hold date and an off hold date is work out how many working days something is on hold.

Now, problem is, not always is something on hold, therefore fields are often blank and then i get a result in query that says "#Error", but i want to use the answer to this expression in another formula, but when error is displayed it makes the other query show error too.

What I want is some sort of If statement or similar so that if no results exist to display "0". Can anyone tell me how to add this in?

I'm a total Access Noob, it took me forever to work out this working days thing (damn access for not being as simple as excel!)

I think my problem might lie in the way the function has been written. I think i might have to modify this to show "0", rather than "error"

This is what ive got in the function.

Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
Dim i As Long 'day counter
Dim dteCurDay As Date
'set i = 1 if you want the first date to count as a full day
'or i = 0 if you do not want the first day to count as a full day

i = 0
dteCurDay = dteStart
Do Until dteCurDay >= dteEnd
'check date against holiday table
If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
'continue checking for weekdays
If Weekday(dteCurDay, vbSunday) <> vbSunday And _
Weekday(dteCurDay, vbSunday) <> vbSaturday Then
i = i + 1
End If
End If
dteCurDay = DateAdd("d", 1, dteCurDay)
Loop
calcWorkDays = i
End Function


Any help would be much appreciated!
Thanks

View 1 Replies View Related

Modules & VBA :: Error (Invalid Use Of Null) In A Function Called From A Form Current Event

Nov 18, 2013

I am struggling trying to execute a function inside a Form_current event to display some stats.

The Function is this:

Code:
Function FlightsByAircraft(Aircraft As Long) As Long
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim str As String
str = "SELECT * FROM tblFlights WHERE AircraftID = " & Aircraft

[Code] ....

The code for the Form_Current event is this:

Private Sub Form_Current()
txtStats1 = FlightsByAircraft(Me.AircraftID)

Very simple. Well, the problem is when I move to a new record, a error message comes up: "Run-time error '94' - Invalid use of Null". It is because the AircraftID is not populated at that time. I tried to insert in the function code something like that:

Code:
If IsNull(Aircraft) then
exit function
else
.... (the DAO.Recordset code)

but it doesn't work.

View 8 Replies View Related

Numeric Field When Null Display Text

Dec 14, 2006

Hi All,

I have a field called PRICE and obviously it is a NUMERIC field as it need to perform calculations such as calculating Total Quantity * Price etc. At the moment the PRICE is inputted manually by the User on a Form and when it has no Price it is simple left blank.

What I wish to include is that on the REPORT when the field is empty it writes the text FOC instead of leaving it empty.

Can you please anyone suggest a way of doing this.

Thanks any help will be much appreciated :o

View 8 Replies View Related

Query Criteria To Display Current Week Only?

Jan 23, 2012

I have a database that lists jobs that need doing, in the jobs table there is a Deadline field, this is a Date Time field that specifies when a job must be finished by.

So I'm trying to make a query, using my limited knowledge, that displays all jobs that need finishing between todays date and the rest of the week.

So I tried this and got nowhere:

Between #(Date())# AND #(Date()+7)#

View 4 Replies View Related

Queries :: Button To Display Current Time (Sign In And Out)

Jan 27, 2015

I have created a button to display the current time ( sign in and out).

It works fine with this code Me.field = time$

However i need a code beneath it to run the actual query that will display this time onto the table...

I have attached an image of one of the query, I need it to run.

View 5 Replies View Related

Tables :: How To Display Default Value Of Date / Time Field For Current Year

Aug 20, 2013

All, using access 2010. How do I display the default value of a date/time field to just the current year instead of using =date() to get the full date.

View 5 Replies View Related

Forms :: Create List Box To Only Display Information From Another Form Related To Current Form

Mar 20, 2014

What I would like to do is create a list box that will only display the information from another form that is related to the current form.So I have a form call Equipment Catalog and that form is related to Equipment features 1 to M relationship and the Equipment Features is related to a Features form M to 1.

So what I want to do is display all the related equipment features in a listbox that is related to the current PK of that form.So if there is only one feature on one form the list box will only display that one item however is there is 6 features on another it will display all 6.I have been trying SQL and Queries but I still can't get it to work.

View 1 Replies View Related

Modules & VBA :: Display Last Record Next To Empty Space On Current Record

May 6, 2015

I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.

This is so the user knows that what they are entering is roughly in line with what has come before.

So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.

Records:

Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2

So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.

View 2 Replies View Related

If A Make-table Query Result Is Null, How To Have A Default Message Appear In Table ?

Jan 21, 2005

Hi everybody,

Beginner here needs help !

I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?

Thanks in advance !

View 1 Replies View Related

Queries :: Delete Main Table Row Depending On Sub-table Row If It Is Null

Dec 25, 2014

i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null.

View 3 Replies View Related

Search Record In Other Table And Show Result On Current Table

Apr 11, 2012

Actually I have a small form of customer details, that i made in excel, the field name mention below,

Customer Details Table
First Name
Last Name
Contact Detail
Address Detail

Postal Code
Last Purchasing Date
Remark

Now i want to make a search form like this

Search Form

Contact Details

& the result is show which I insert the contact number.......

View 1 Replies View Related

Forms :: Display Some Fields From Master Table And Only Last Entry From Child Table

Jul 1, 2013

I have 2 tables, master & child. with a one to many relationship.

On one of my forms I want to display some of the fields from the master table and only the last entry from my child table.

How would i accomplish this?

View 5 Replies View Related

Updating Table In Second Db From Current Db

Mar 15, 2006

I am OK creating a SQL query to update a table in a second database from a look-up table in the second database, and I can create a query to update a table in the current database from a look-up table in the current database - simple.

However, I am trying to create an Update Query to update a table in a second database from a look-up table in the current database.

(and I want to avoid copying the look-up table to the second database.)

Does anyone know how to do this?

View 8 Replies View Related

Cannot Insert A Null Value Into Table Record

May 2, 2007

Using the sql profiler gives me no clue.

This is the insert query from the profiler.



exec sp_executesql N'INSERT INTO "ENVIS_GSD".."wat_springflow" ("result_dt","site_id","result_va","remark_tx") VALUES (@P1,@P2,@P3,@P4,@P5,@P6)', N'@P1 datetime,@P2 int,@P3 float,@P4 nvarchar(4),@P5 varchar(3),@P6 datetime', 'Feb 10 2009 12:00:00:000AM', 3000723, 8.000000000000000e-001, N'test'



The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.

Somehow up to a few weeks ago those two columns were updated.

Tracing the code and using the immediate window the user_name variable stores the login name corectly.

Someone mentioned "error 28" any ideas?

Thanks in advance

View 1 Replies View Related

Add Field To Table Then Check For Null

Feb 10, 2008

I need to add a Yes/No field to a table. I know I can do this in design view. Next I want to loop through the table and set it to Yes if certain fields are null. What I need help with is the VBA looping part and setting the boolean field to Yes if the fields being checked are null.

View 3 Replies View Related

Problem With Null Or Empty Table

Dec 12, 2005

I have the following problem:

I have a make table query that sum a total amount in order to have only the total in the table field.

However, when the total is 0.00, the make table query says "you are about to pos 0 rows" instead of postin 1 row with 0.00.

How can I set the query in order to have a 0 in the table when the total is 0 instead of having nothing posted ?

I have tried the is null, is empty, is missing, nz and nothing seems to work.

View 1 Replies View Related

Modules & VBA :: Switch Function - A Null Makes Whole Column Null

Nov 16, 2014

I do not understand what is happening here. I have foll0wing line in a calculated query field:

m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010

this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.

But if I do this:

m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example

then the entire column is set to Null

View 2 Replies View Related

Copy Current Record To Different Table

Dec 4, 2006

Is it possible to copy the current record on a form to a different table?
Example:

Form Name = Training Orders bound to a table with the same name.
2nd Table Name = History

I need to export certin fields from the Training Orders Form into the History Table. Below is the way I am trying to make it happen, but it does not work.

With Me.RecordsetClone
.AddNew
![Forms]![History]![Last Name] = Me.[lastname]
.Update


Me.Bookmark = .LastModified




End With


End Sub

View 1 Replies View Related

Append Current Record To Another Table

Oct 29, 2006

Im trying to make a button that sends the current open record information to another table. I created an append Query but it is pulling nothing. Can someone help me figure out how to send the current open file to a seperate table?

View 4 Replies View Related

Forms :: Add Current Date To Table

Apr 7, 2013

I want to create a form that will update several tables at the same time.A field that is important in each table is "ID" and "DATE" date being the current date the record is being created and ID is the Unique ID of user creating the record.These 2 fields should be incorporated in the different tables without the user having to identify himself every day or to add the date all the time ( I want the system date to be automatically used)

View 4 Replies View Related

Add Record From Current Form To A Table?

Aug 26, 2013

I have a database that includes the following items related to my question:

- Tables: tblCases, tblPeople, tblPeoplePerCase,

- Forms: frmCases, frmPeople, subfrmPeoplePerCase, frmSearchPeople

What I'm trying to do is automate the process of adding a selected person to a particular case. What I'm thinking is that frmCases (which includes subfrmPeoplePerCase) will include a button to add a person to the case. Once clicked, frmSearchPeople would open. Once a person is selected and frmPeople displays their information, a button would then be displayed/enabled on frmPeople to add them to the current case in frmCases. Once this button is clicked, it would display a message box asking to confirm if PersonID should be added to CaseID. If confirmed, the information is then appended to tblPeoplePerCase (which includes PersonID, CaseID). The user is then brought back to frmCases and the subfrmPeoplePerCase displays the newly related information.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved