Can not use numeric expression in MS Access SQL
0
I just stucked, is something wrong with P.NetPrice * 100 in SQL, I have tried it in MSQL SMS and it worked for me, how can I do that in Access.
Actually I wanted to do : ((P.NetPrice * 100) / I.Quantity)
Any help would be nice :)
Thanks
Update
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS Price
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
works in query mode after entering parameter of the cmbReceipt
but not working in form view the same query asking for parameter NetPrice
Any tips ?
Update
Just forgot to call last column as NetPrice after I've added a numeric expression, that was the reason access kept asking for parameter.
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS NetPrice
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
SOLVED NOW
sql ms-access
asked Nov 15 '18 at 8:23
LYRLYR
227
add a comment |
0
I just stucked, is something wrong with P.NetPrice * 100 in SQL, I have tried it in MSQL SMS and it worked for me, how can I do that in Access.
Actually I wanted to do : ((P.NetPrice * 100) / I.Quantity)
Any help would be nice :)
Thanks
Update
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS Price
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
works in query mode after entering parameter of the cmbReceipt
but not working in form view the same query asking for parameter NetPrice
Any tips ?
Update
Just forgot to call last column as NetPrice after I've added a numeric expression, that was the reason access kept asking for parameter.
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS NetPrice
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
SOLVED NOW
sql ms-access
asked Nov 15 '18 at 8:23
LYRLYR
227
add a comment |
0
0
0
I just stucked, is something wrong with P.NetPrice * 100 in SQL, I have tried it in MSQL SMS and it worked for me, how can I do that in Access.
Actually I wanted to do : ((P.NetPrice * 100) / I.Quantity)
Any help would be nice :)
Thanks
Update
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS Price
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
works in query mode after entering parameter of the cmbReceipt
but not working in form view the same query asking for parameter NetPrice
Any tips ?
Update
Just forgot to call last column as NetPrice after I've added a numeric expression, that was the reason access kept asking for parameter.
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS NetPrice
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
SOLVED NOW
sql ms-access
asked Nov 15 '18 at 8:23
LYRLYR
227
I just stucked, is something wrong with P.NetPrice * 100 in SQL, I have tried it in MSQL SMS and it worked for me, how can I do that in Access.
Actually I wanted to do : ((P.NetPrice * 100) / I.Quantity)
Any help would be nice :)
Thanks
Update
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS Price
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
works in query mode after entering parameter of the cmbReceipt
but not working in form view the same query asking for parameter NetPrice
Any tips ?
Update
Just forgot to call last column as NetPrice after I've added a numeric expression, that was the reason access kept asking for parameter.
SELECT R.Name, P.Name, I.Quantity, (P.NetPrice/100)*I.Quantity AS NetPrice
FROM Receipt AS R INNER JOIN (Product AS P INNER JOIN Ingredients AS I ON P.ID = I.ProductID) ON R.ID = I.ReceiptID
WHERE (((R.ID)=Forms![frmReceipt]![cmbReceipt]));
SOLVED NOW
sql ms-access
sql ms-access
asked Nov 15 '18 at 8:23
LYRLYR
227
asked Nov 15 '18 at 8:23
LYRLYR
227
edited Nov 15 '18 at 10:48
LYR
asked Nov 15 '18 at 8:23
LYRLYR
227
asked Nov 15 '18 at 8:23
LYRLYR
227
asked Nov 15 '18 at 8:23
LYRLYR
227
227
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
It is rather the parameter causing the trouble. Specify it:
PARAMETERS [Forms]![frmReceipt]![cmbReceipt] Long;
SELECT R.Name, …
Edit:
Also, you must select all the fields you wish use:
SELECT R.Name, P.Name, I.Quantity, P.NetPrice, (P.NetPrice/100)*I.Quantity AS Price
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53315105%2fcan-not-use-numeric-expression-in-ms-access-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
It is rather the parameter causing the trouble. Specify it:
PARAMETERS [Forms]![frmReceipt]![cmbReceipt] Long;
SELECT R.Name, …
Edit:
Also, you must select all the fields you wish use:
SELECT R.Name, P.Name, I.Quantity, P.NetPrice, (P.NetPrice/100)*I.Quantity AS Price
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
|
show 1 more comment
1
It is rather the parameter causing the trouble. Specify it:
PARAMETERS [Forms]![frmReceipt]![cmbReceipt] Long;
SELECT R.Name, …
Edit:
Also, you must select all the fields you wish use:
SELECT R.Name, P.Name, I.Quantity, P.NetPrice, (P.NetPrice/100)*I.Quantity AS Price
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
|
show 1 more comment
1
1
1
It is rather the parameter causing the trouble. Specify it:
PARAMETERS [Forms]![frmReceipt]![cmbReceipt] Long;
SELECT R.Name, …
Edit:
Also, you must select all the fields you wish use:
SELECT R.Name, P.Name, I.Quantity, P.NetPrice, (P.NetPrice/100)*I.Quantity AS Price
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
It is rather the parameter causing the trouble. Specify it:
PARAMETERS [Forms]![frmReceipt]![cmbReceipt] Long;
SELECT R.Name, …
Edit:
Also, you must select all the fields you wish use:
SELECT R.Name, P.Name, I.Quantity, P.NetPrice, (P.NetPrice/100)*I.Quantity AS Price
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
edited Nov 15 '18 at 10:41
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
answered Nov 15 '18 at 8:31
GustavGustav
30.4k51937
30.4k51937
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
|
show 1 more comment
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
Did it but did not helped. Access automatically asks for parameter in popup after running Query, after entering the right parameter value the error comes.
– LYR
Nov 15 '18 at 8:42
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
And if you remove the parameter? And what is the exact error message?
– Gustav
Nov 15 '18 at 10:08
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
Now I figured out the basic problem, you were right there was problem with parameter from form. It is gone now if I am running the Query in SQL query mode in access. The other problem is that I am using this Query after changing value in combobox. In form the query asks a parameter value of NetPrice. I am just confused, the same query works in query mode, but not in form mode.
– LYR
Nov 15 '18 at 10:30
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
If you call the query from code, it will not read the parameter, even if the form is open. If you open the query as "stand-alone" and the form is open, it will read the parameter.
– Gustav
Nov 15 '18 at 10:36
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
Oh, I see now. It was because the query did not have the parameter NetPrice, after I've added numeric expression I forgot to call that as NetPrice, used only Price. Now it is clear and working. Anyway thanks to get me there :)
– LYR
Nov 15 '18 at 10:43
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53315105%2fcan-not-use-numeric-expression-in-ms-access-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
