Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-13-2018, 04:47 AM   #1
kobiashi
Newly Registered User
 
Join Date: May 2018
Posts: 177
Thanks: 31
Thanked 0 Times in 0 Posts
kobiashi is on a distinguished road
assigning an integer using Null Value

hi

i have a query see below

Code:
SELECT Table_Bogies.Bogie_ID, Table_Bogies.BogieSerialNumber, Table_Bogies.BogieShortName, Table_Bogies.bogieType_IDFK, Table_Manufacturers.ManufacturerName, Table_Bogies_M2M.Stores_IDFK, Table_Bogies_M2M.DateAdded, Table_Stores.Location
FROM Table_Vehicles RIGHT JOIN (Table_Stores RIGHT JOIN (((Table_Manufacturers RIGHT JOIN Table_AssetComponentType ON Table_Manufacturers.Manufacturer_ID = Table_AssetComponentType.Manufacturer_IDFK) RIGHT JOIN Table_Bogies ON Table_AssetComponentType.AssetCompType_ID = Table_Bogies.bogieType_IDFK) RIGHT JOIN Table_Bogies_M2M ON Table_Bogies.Bogie_ID = Table_Bogies_M2M.Bogie_IDFK) ON Table_Stores.Stores_ID = Table_Bogies_M2M.Stores_IDFK) ON Table_Vehicles.Vehicle_ID = Table_Bogies_M2M.Vehicle_IDFK;

i am trying to insert a default value into Stores_IDFK as an integer, so the Table_Stores.Location self populates, as you can see, the stores_IDFK is the foreign key linked to Table_stores by a right join

when i use the NZ command in SQL on Stores_IDFK, it inserts "1", but the location field doesnt update, but when i manually populate Stores_IDFK with "1", it does, am i using the NZ command wrong?

kobiashi is offline   Reply With Quote
Old 10-13-2018, 05:36 AM   #2
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,811
Thanks: 7
Thanked 309 Times in 299 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: assigning an integer using Null Value

The Nz() function does not change a field, it just changes what is returned from that field if the field is Null.
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 10-13-2018, 05:41 AM   #3
kobiashi
Newly Registered User
 
Join Date: May 2018
Posts: 177
Thanks: 31
Thanked 0 Times in 0 Posts
kobiashi is on a distinguished road
Re: assigning an integer using Null Value

thanks for the reply,


is there a way of auto filling a field from the query?

kobiashi is offline   Reply With Quote
Old 10-13-2018, 05:50 AM   #4
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,811
Thanks: 7
Thanked 309 Times in 299 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: assigning an integer using Null Value

To change a field in a table with a query requires either an update query or an append query. You are using a read type query (Select). Maybe if you describe what you are trying to accomplish (without using any jargon we won't understand) someone could offer another approach that would work for you.
__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Old 10-13-2018, 06:03 AM   #5
kobiashi
Newly Registered User
 
Join Date: May 2018
Posts: 177
Thanks: 31
Thanked 0 Times in 0 Posts
kobiashi is on a distinguished road
Re: assigning an integer using Null Value

Thanks for the reply, I e done it from the form instead.

Sent from my ONEPLUS A6003 using Tapatalk
kobiashi is offline   Reply With Quote
Old 10-13-2018, 06:07 AM   #6
RuralGuy
AWF VIP
 
RuralGuy's Avatar
 
Join Date: Jul 2005
Location: @ 8300' in the Colorado Rockies
Posts: 13,811
Thanks: 7
Thanked 309 Times in 299 Posts
RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice RuralGuy is just really nice
Re: assigning an integer using Null Value

Excellent decision! Thanks for the feedback. Did you use the thread tools at the top of the thread to mark this thread as Solved?

__________________
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please post back to this Forum so all may benefit.
Teaching is not filling a bucket but lighting a fire.
RuralGuy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigning a Null value to a Control grenee Modules & VBA 11 12-10-2017 07:34 PM
[SOLVED] Select Case with Integer that could be null Rx_ SQL Server 1 06-01-2015 08:32 AM
Insert null values into Date and Integer data type drPavlovic Forms 7 09-24-2006 05:29 PM
Send Null values in Date and Integer data type ??? drPavlovic Modules & VBA 1 09-23-2006 07:35 AM
Query: Assigning a null value illy2k Queries 5 06-06-2003 12:50 AM




All times are GMT -8. The time now is 05:28 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World