rbaggins
Posts: 7
|
| Posted: 09/12/2009, 12:39 PM |
|
This should work but I am stumped.
Here is my SQL in the Query Builder:
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND User_id = '{UserLogin}'
AND ( (User_id IS NULL) )
I am passing the user login to a form successfully. What I am trying to accomplish is this: When a user selects either a record they have written (their user login is in the record) or if the userlogin field is a null they could edit/submit that record as well.
If a another user selects the record they can only view the record.
If I remove the "AND ( (User_id IS NULL) )" from the query it works for matching the userlogin just fine, but I cannot seem to get the null records with the populated matching UserLogin.
Any help would be appreciated !
|
 |
 |
ReneS
Posts: 225
|
| Posted: 09/13/2009, 4:00 AM |
|
Hi,
First of all, i think, User_id cannot be UserLogin AND NULL at the same time. So you should use OR. Second, I am not sure if the syntax is right for User_id IS NULL, but try different syntaxes because i am not a programmer....
Try Something like this:
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND (User_id = '{UserLogin}' OR User_id= "NULL")
Rene
|
|
|
|
Joachim Uersfeld
|
| Posted: 09/13/2009, 7:15 AM |
|
In MS SQL Server:
SELECT * FROM master_census_srce WHERE SigFee_id = {SigFee_id}
AND ((User_id = '{UserLogin})' OR (User_id IS NULL))
ReneS wrote:
> AND (User_id = '{UserLogin}' OR User_id= "NULL")
|
|
|
|
rbaggins
Posts: 7
|
| Posted: 09/14/2009, 11:01 AM |
|
Thanks for your help.
Here is what I tried based on your input:
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND ( User_id = '{UserLogin}' OR User_id= "NULL" )
Which Results in:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NULL'. (Microsoft OLE DB Provider for ODBC Drivers)
I am using the visual query builder in the properties tab. - in the Data Source for the form.
Any help would be really appreciated -
-Rob
|
|
|
|
ReneS
Posts: 225
|
| Posted: 09/14/2009, 1:20 PM |
|
Hi,
Try Joachims solution. Switch in visual query builder to sql
Rene
|
|
|
|
rbaggins
Posts: 7
|
| Posted: 09/14/2009, 2:20 PM |
|
Thanks for all your input and hanging in there with me.
Still getting an error message in the form:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NULL'. (Microsoft OLE DB Provider for ODBC Drivers)
Using this code:
SELECT *
FROM master_census_srce WHERE SigFee_id = {SigFee_id} AND ( User_id = '{UserLogin}' OR User_id= "NULL" )
Switched from table to SQL - same result I have enclosed a screen shot of the visual query builder/sql option
|
|
|
|
Joachim Uersfeld
|
| Posted: 09/14/2009, 10:55 PM |
|
This SQL is wrong: ... OR User_id= "NULL"
This SQL works: ... OR User_id IS NULL
rbaggins wrote:
> Thanks for all your input and hanging in there with me.
> Still getting an error message in the form:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
> 'NULL'. (Microsoft OLE DB Provider for ODBC Drivers)
> Using this code:
> SELECT *
> FROM master_census_srce WHERE SigFee_id = {SigFee_id} AND ( User_id =
> '{UserLogin}' OR User_id= "NULL" )
|
|
|
|
ReneS
Posts: 225
|
| Posted: 09/15/2009, 1:49 AM |
|
Hi,
As I said in my previous post:
Try JOACHIM solution. (thanks Joachim!)
Rene
|
|
|
|
rbaggins
Posts: 7
|
| Posted: 09/16/2009, 11:36 AM |
|
Thanks everyone for your input. I have the sql working.
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND User_id = '{UserLogin}'
OR ( (User_id IS NULL) )
However the insert capability in the form becomes disabled for all the records. I want the insert capability in the form when the above condition is met.
When I publish the application I get this warning from CodeCharge:
"Warning:Update is allowed for Record master_census_srce. Unable to build Update based on the current Datasource settings (joins, SP or custom SQL). Change the Allow Update property to disable Update or specify Custom Update."
I have tried using the custom update but with no success. Any Ideas?
Thanks much.
-Rob
|
|
|
|
