Search results

Resolved I have managed to resolve this issue with a lot of help so many thanks. I created an additional query. The SQL that worked as follows: SELECT tblSoftware.Vendor, qry_LICENSE_AVAILABILITY.Description, qry_LICENSE_AVAILABILITY.LicenseKey, qry_LICENSE_AVAILABILITY.NumOfLicensePurch...

Here you go Here you go Keith: SELECT tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch, Nz([SumOfNumberOfLicences],0) AS [License Allocation], nz(([NumOfLicensePurch]-[SumOfNumberOfLicences]),[NumofLicensePurch]) AS [License Available] FROM...

Expression Keith I should have stated in my post that I had already changed totals to expression. Still received error? Thanks, Phil.

Error Tried: License Allocation: nz([SumOfNumberOfLicences],0) Error:.............specified expression Nz([SumOfNumberOfLicences]) as part of an aggregate function............... Any ideas? thanks, Phil.

Cheers Thanks, I will give that a try. Phil

Great Effort Keith many thanks for your efforts. I have ended up with this: SELECT tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch, qryALLOCATIONS_LICENSE.SumOfNumberOfLicences AS [License Allocation]...

Progress? I tried: License Available: IIf(IsNull([NumberOfLicenceAllocated]),[NumOfLicensePurch]) If the number of licences allocated is null then License Available mirrors the number of licences purchased. (good) However: Where licenses HAVE been allocated the field is blank AND Number...

Not Quite I have this that nearly works: SELECT tblSoftware.Vendor, tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch, tblSoftwareAllocation.NumberOfLicences, [tblLicenseDetails].[NumOfLicensePurch]-[tblSoftwareAllocation].[NumberOfLicences] AS [License...

Import Create your blank database then from 'File' select option to 'Get External Data' then 'Import'. Browse to your exisiting DB and select the items you wish to import. Regards, Phil.

Syntax I have got my knickers in a twist. Tried a number of variations. My latest failure is posted below. Can somebody spot the syntax flaw: Select Description, [NumOfLicensePurch], Total_Allocated,Iif(Is Null([Total_Allocated]),([NumOfLicensePurch]),([NumOfLicensePurch]-[Total_Allocated]))...

Sample DB Thanks I will try the new syntax. I have attached a sample DB (stripped down). I have created a number of licenses previously trying to nail the software / license display issue. In addition if you note on form 'Software License Allocation'. When you allocate the license the details...

Syntax Error Query 1 ran OK. Query 2 returns a syntax error on the IIF? I have substituted the correct field names: Select Description, [NumOfLicensePurch], Total_Allocated,IIF(Total_Allocated is null,[NumOfLicensePurch],([NumOfLicensePurch]-Total_Allocated])) as License_Available From...

Thanks Keith I will give that a whirl now and post back. Phil.

Thanks I think I have tried that one. I do not want to display license available as such, more an overview of all software and the current license status. I will give it a whirl and post back. Thanks, Phil.

Thanks Keith that worked a treat many thanks. If you fancy a challenge I have been scratching my head over this one for months: I have 3 tables. tblSoftware tblLicenseDetails tblSoftwareAllocation tblSoftware lists all software. tblLicenseDetails lists licenses details for the software...

I have 2 tables tblComputers & tblComputerAllocations tblcomputers has 85 records & tblComputerAllocations has 71. I would like a query that returns the 14 computers that have no allocations. I tried a union query on field ComputerID but i must misunderstand its use!! Thanks in advance, Phil.

Solved I have managed to resolve this phantom issue. I simply copied all the data to a new database..................and it worked? As with previous versions I will post this DB to the Sample Database forum when complete. Thanks to everyone who took time to respond. Phil.

Head Scratching Time The combo 'ComputerID' is based on a lookup query qryComputerIdLOOKUP. The lookup query has 4 fields: ComputerID ComputerMake ComputerModel SerialNumber If I run the query it runs fine and displays data for all fields. The AfterUpdate event on the combo ComputerID is...

Error I believe I have made an error. I will have a closer look. My post still stands regards handling NULL however so if you gurus have any ideas!! Thanks, Phil.

Null Gary you effort worked a treat. Just one question. If the field to be populated has no data access returns an error: Run Time Error 94 Invalid Use of Null Here is what I have on the after update: Make = ComputerId.Column(2) Model = ComputerId.Column(3) Serial# = ComputerId.Column(4)...